How To Retrieve The Result From A Stored Procedure That Executes A Dynamically Built Up Sql Query?
We have a sort of complex user structure in the sense that depending on the type of user the data resides in different tables. Therefor I needed a stored procedure that finds out what table to look for a certain column in. Below is such a stored procedure and it works like it should but my problem is that I don't know how to retrieve the result (which should be a string so can't use RETURN).
I've tried using an OUTPUT variable but since I just run EXEC (@statement) in the end I can't really set an output variable the common way (as in EXEC @outputVariable = PMC_User_GetUserValue(arg1, arg2..)) or can I?
I have also tried to use SELECT to catch the result somehow but no luck and Google didn't help either so now I'm hoping for one of you... Notice that you don't have to bother about much of the code except for the end of it where I want it to return somehow or figure out a way to call this stored procedure and retrieve the result.
Thanks in advance
ripern
-- Retrieves the value of column @columnName for credential id @credID
ALTER PROCEDURE [dbo].[PMC_User_GetUserValue]
@credID int,
@columnName nvarchar(50)
AS
DECLARE @userDataTable nvarchar(50)
DECLARE @userDataID int
DECLARE @statement nvarchar(500)
SET @statement = ' '
SET @userDataID =
(SELECT PMC_UserMapping.fk_userDataID
FROM PMC_UserMapping
INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id
WHERE PMC_User.fk_credentials_id = @credID)
SET @userDataTable =
(SELECT PMC_UserType.userDataTable
FROM PMC_UserType
INNER JOIN PMC_UserMapping ON PMC_UserType.id = PMC_UserMapping.fk_usertype_id
INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id
WHERE PMC_User.fk_credentials_id = @credID)
SET @statement = 'SELECT ' + @columnName + ' AS columnValue FROM ' + @userDataTable + ' WHERE id=' + convert(nvarchar, @userDataID)
-- Checks whether the given column name exists in the user data table for the given credential id.
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@userDataTable AND COLUMN_NAME=@columnName )
BEGIN
EXEC (@statement)
END
View Complete Forum Thread with Replies
Related Forum Messages:
Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark
View Replies !
OLEDB Results Executes Stored Procedure
Hello, I would like the values returned from a resultset be executed by a stored procedure (which adds the value) in Business Intelligence Development Studio; is that possible? Essentially the OLEDB Source would provide values to a destination, which the destination calls an add stored procedure to add the results? Is that even feasible? Thanks,
View Replies !
Built-in Stored Procedure
Vagiz writes "I hope I can clearly picture my problem: There is a built-in stored procedure - sp_addextendedproperty. And one of the properties that it takes - @value=N'False'. I duplicated one database which was created by somebody else and for some reason in the copy version it is written as @value=N'False' while in the original table it is written as @value=False. There are three characters difference. Is it a significant difference? Because when I'm trying to open the table in Access it says that "The stored procedure executed successfully but did not return records." and I tend to think that since those three characters were the only difference in the script they are causing some problems. Why is that? When I'm running the table from SQL Analyzer it shows the whole table just fine. Thanks"
View Replies !
SSIS Package Executes Within BIDS But Not From A Stored Procedure
I have a SSIS package that contains a DTS 2000 package in it. The DTS 2000 package imports data into several tables from an ODBC data source. When I execute the package through BIDS, no problems. Everything works great. I am now trying to execute the SSIS package in my stored procedure & it gives me the following error: Error: 2007-01-30 11:54:24.06 Code: 0x00000000 Source: Populate IncrTables Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user. at DTS.PackageClass.Execute() at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread() End Error I did a search for this & found KB 904796. It had the exact error message but I don't believe my packages uses 2000 metadata services. Just to be safe, I reinstalled the backward compatibility features & the DTS 2000 tools on the server. That still did not fix anything. I found another forum that suggested loading the DTS 2000 package internally, which I did & it did not fix anything. I am using a password for the protection level so that is not causing my issue. Does anyone else have any suggestions as to what I might be able to try? SQL 2005 Dev Ed SP1 & post SP1 hotfixes installed Win 2k3 server Thanks! John
View Replies !
How To Call A Stored Procedure Just After A TableAdapter's INSERT Command Executes
All- Is there a way that I can embedd a call to a stored procedure into an existing INSERT section in a table adapter? Say my objective is to call a stored procedure called personfill automatically RIGHT AFTER the TableAdapter inserts a row into the person table. One catch is that the stored procedure must be sent the value of unique identifier field person_id, which was created for the new person record automatically by the db. (If this is not possible to do, I might try using a TRIGGER in the person table.) Below is the INSERT code of the TableAdapter. My guess is that if I could call a procedure, I would want to put the call between lines 12 and 13. Your comments would be most appreciated!!! -Kurt1 <InsertCommand> 2 <DbCommand CommandType="Text" ModifiedByUser="false"> 3 <CommandText>INSERT INTO [person] ([family_id], [circle_id], [person_type_id], [last], [first], [username], [password]) VALUES (@family_id, @circle_id, @person_type_id, @last, @first, @username, @password)</CommandText> 4 <Parameters> 5 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@family_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="family_id" SourceColumnNullMapping="false" SourceVersion="Current" /> 6 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@circle_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="circle_id" SourceColumnNullMapping="false" SourceVersion="Current" /> 7 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@person_type_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="person_type_id" SourceColumnNullMapping="false" SourceVersion="Current" /> 8 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@last" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="last" SourceColumnNullMapping="false" SourceVersion="Current" /> 9 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@first" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="first" SourceColumnNullMapping="false" SourceVersion="Current" /> 10 <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@username" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="username" SourceColumnNullMapping="false" SourceVersion="Current" /> 11 <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@password" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="password" SourceColumnNullMapping="false" SourceVersion="Current" /> 12 </Parameters> 13 </DbCommand> 14 </InsertCommand> 15 <SelectCommand> 16 <DbCommand CommandType="Text" ModifiedByUser="true"> 17
View Replies !
SQL Query To Retrieve Result Like This
I am using SQL Server 2005. I have a table with records like this: Date EmpID ADCode ADAmount ---------------------------------------------------------------------- 01-Jul-07 101 GPF 150 01-Jul-07 102 GPF.ADV 100 01-Jul-07 103 GPF 250 01-Jul-07 103 GPF.ADV 200 01-jul-07 104 GPF 300 I want to show results like this using a single SQL query: Date EmpID GPF GPF.ADV ----------------------------------------------------------- 01-Jul-07 101 150 0 01-Jul-07 102 0 100 01-Jul-07 103 250 200 01-Jul-07 104 300 0 I tried: select PaySlipDate,EmpID, case ADCode when 'GPF' then ADAmount else 0 end GPF, case ADCode when 'GPF.ADV' then ADAmount else 0 end 'GPF.ADV' from EmpSalaryRecord It is showing like this: Date EmpID GPF GPF.ADV ----------------------------------------- 01-Jul-07 101 0 0 01-Jul-07 101 150 0 01-Jul-07 102 0 100 01-Jul-07 103 0 0 01-Jul-07 103 250 0 01-Jul-07 103 0 200 It is showing multiple records of each employee for each date. First a record with GPF and GPF.ADV both zero and then records with values. I want a single record for each date and employee.
View Replies !
How To Retrieve Only Nth Row As A Result By Writing One Query.
How to retrieve only nth row as a result of execution of one query? For example: Lte the table be: SNo SudentName Marks 001 Ashok kumar 67 002 Anderson 70 003 Alfred 60 004 Ameeruddin 65 005 Kalyan Kumar. 69 Now the Query is: How is the 3rd ranker. The answer must be Ashok kumar. How to write this query in SQL Server.
View Replies !
Stored Procedure In Database X, Executes Stored Procedure In Database Y, Wrapped In Transaction?
Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?
View Replies !
Dynamic Built Where Clause - Error In Stored Procedure
I'm sending a stored procedure a number of parameters, including an int for a foreign key field. I'm trying to dynamically build the Where Clause of the query. I'm getting the error: Syntax error converting the varchar value 'wo_dutfk = ' to a column of data type int. NOTE: wo_dutfk is an int field within the WO table. Here's the code: CREATE PROCEDURE dbo.ewo_sp_WOLookup ( @WODUTFK int=NULL, @WOENDDATE datetime = NULL, @WOSTATUS char(10) = NULL, @DEBUG int=0 ) AS Declare @intErrorCode int, @chvQuery varchar (8000), @chvWhere varchar (8000) select @intErrorCode = @@ERROR, @chvQuery='SET QUOTED_IDENTIFIER OFF select * from WO', @chvWhere='' -- DUT is specified If @intErrorCode = 0 and @WODUTFK is not NULL Begin set @chvWhere = @chvWhere + 'wo_dutfk = '+@WODUTFK End IF @intErrorCode=0 and Len(@chvWhere)>0 Begin set @chvQuery = @chvQuery + ' WHERE ' + @chvWhere select @intErrorCode=@@ERROR END IF @DEBUG<>0 select @chvQuery Query IF @intErrorCode=0 Begin exec (@chvQuery) select @intErrorCode=@@ERROR End return @intErrorCode GO --- Any suggestions on how to fix? Thanks, peter
View Replies !
Same Statement Executes 10 Times Faster As Raw Sql In Query Analyzer Then In A Stored Proc
Hi, I apologize for the long post but I am trying to give as much information as I can about the steps I've taken to troubleshoot this. We have a stored procedure that builds a sql statement and executes it using the Execute command. When I execute the stored procedure through query analyzer it takes close to 5 seconds to execute. When I print out the exact same statement and execute it directly in query analyzer as "raw sql", it takes 0.5 seconds - meaning it takes 10 times longer for the code to execute in the stored proc. I altered the stored proc to execute the printed sql instead of building but it still takes the full 5 seconds and there were no changes in the execution plan. This makes me confident that the issue is not caused by the dynamic sql. I've used with recompile to make sure that the stored procedure caches the most recent execution plan. When I compare the execution plans, the stored proc uses a nested loop whereas the raw sql statement uses a hash join. Seeing that, I added the hash hint to the stored proc and doing so brought down the execution time down from 5 secs to 2 secs but still the raw sql statement uses a clustered index whereas the stored proc uses a non-clustered index and that makes the statement 4 times slower. This proves how efficient clustered indexes are over non-clustered ones, but it doesn't help me since, as far as I know, I can't force SQL Server to use the clustered index. Does anyone know why sql server is generating such an inefficient execution plan for the stored proc compared to the execution plan that it generates when executing the raw sql statement? The only thing I can think of is that some stats are not updated and that somehow throws off the stored proc. But then again, shouldn't it affect the raw sql statement? Thank you, Michael Tzoanos
View Replies !
Dynamically Execute Stored Procedure
Hello,I was wondering if it is possible to dynamically execute a stored procedure; for example, in SQL, you can do:insert into Table1( id, name)select id, namefrom Table2Can you do something like:exec spProc @id = id, @name = namefrom Table1Or something like that? I know I can select a row at a time and execute, or write a program, but I was looking to see if there was an easier way.Thanks.
View Replies !
Retrieve Stored Procedure
I need some help retrieving the stored procedure listed below. I would like to use a drop list to select the variable "UserName" and textboxes to send the variables "StartingDate" and "EndingDate". How do I pass these variables and then have the results show up in a gridview? Any advice would be greatly appreciated. CREATE PROCEDURE [dbo].[aspnet_starterkits_GetTimeEntryUserReportByUserNameAndDates] @UserName NVARCHAR(256), @StartingDate datetime, @EndDate datetimeASDECLARE @UserId AS UNIQUEIDENTIFIERSET NOCOUNT ONSELECT @UserId=UserId FROM aspnet_users WHERE UserName=@UserNameSELECT @UserName as UserName, SUM (timeentryDuration) AS TotalDuration,SUM (timeentryOvertime) AS TotalOvertimeHoursFROM aspnet_starterkits_TimeEntryWHERE aspnet_starterkits_TimeEntry.TimeEntryUserId=@UserId AND TimeEntryDate between @StartingDate and @EndDate
View Replies !
Stored Procedure Dynamically Determine Columnname
I want to dynamically determine which column I select data from. The table I want to select from looks like this:tblSexualitySexualityID int PKEN nvarchar(20)NL nvarchar(20)DE nvarchar(20)Based on the value of variable @LanguageColumnName I want to select the value from either column "EN", "NL" or "DE"In the sample below the value of @LanguageColumnName is "NL".Unfortunately the value of sexualityname is ALWAYS: "tblSexuality.NL" and not the value from that specific column...how can I alter my procedure so that it does the select the value from that column?ALTER PROCEDURE [dbo].[spFindUsers]@LanguageColumnName nvarchar(3),@startRowIndex int,@maximumRows intASBEGINSET NOCOUNT ON; declare @tblSexualityName nvarchar(40)set @tblSexualityName='tblSexuality.' + @LanguageColumnName SELECT UserName,UserCode,ThumbNailPicture,BirthDate,ShowAgeOnly,IsMale,NearestBigCity,DistanceToNearestBigCity,Description,IsDonator,IsVIP,SexualityName FROM(SELECT ROW_NUMBER() OVER (ORDER BY UserCode) as RowNum,tblUserData.UserName,UserCode,IsDonator,IsVIP,Description,BirthDate,IsMale,ShowAgeOnly,tblCountries.CountryPicture,@tblSexualityName as sexualityname,tblCountries.CountryName,ThumbNailPicture,LastActivityDate,UserRanking,NearestBigCity,DistanceToNearestBigCity FROM aspnet_Users INNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserID INNER JOIN tblCountries ON tblUserData.Country=tblCountries.CountryID INNER JOIN tblSexuality ON tblUserData.Sexuality=tblSexuality.SexualityID) as MemberInfoWHERE RowNum > @startRowIndex AND RowNum <= (@startRowIndex + @maximumRows)END
View Replies !
How To Dynamically Create SQL Inside A Stored Procedure?
I am having problem with 'TOP @pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors. Can someone please tell me how I could use @pageSize here so that it dynamically determines the 'n' of 'TOP n' ? ALTER PROCEDURE dbo.spGetNextPageRecords ( @pageSize int, @previousMaxId int ) AS /* SET NOCOUNT ON */ SELECT Top @pageSize ProductId, ProductName FROM Products WHERE (ProductID > @previousMaxId) order by ProductId RETURN
View Replies !
Building Where Clause Dynamically In Stored Procedure
Hello All, I have created SP in SQL 2K5 and make the where clause as parameter in the Sp. i am passing the where clause from my UI(ie ASP.NET), when i pass the where clause to SP i am not able to fetch the results as per the given criteria. WhereClause from UI: whereClause="where DefectImpact='High'" SQL Query in SP: SELECT @sql='select * from tablename' Exec(@sql + @whereClause ) Here i am not able to get the results based on the search criteria. Instead i am getting all the results. Please help me in this regard. Thanks, Subba Rao.
View Replies !
Dynamically Specify Server And Database In Stored Procedure
I am writing Stored Procedures on our SQL 2005 server that will link with data from an external SQL 2000 server. I have the linked server set up properly, and I have the Stored Procedures working properly. My problem is that to get this to work I am hardcoding the server.database names. I need to know how to dynamically specify the server.database so that when I go live I don't have to recompile all of my stored procedures with the production server and database name. Does anyone have any idea how to do this? EXAMPLE: SELECT field1, field2 FROM mytable LEFT OUTER JOIN otherserver.otherdatabase.dbo.othertable OBJECTIVE: Replace 'otherserver.otherdatabase.dbo.othertable' with some other process (dbo.fnGetTable('dbo.othertable')????) Thanks for any help
View Replies !
Retrieve A Dataset From Stored Procedure
Hi, Can anyone please help me solve this problem. My functions works well with this stored procedure: CREATE PROCEDURE proc_curCourseID@studentID int ASSELECT * FROM StudentCourse WHERE mark IS NULL AND studentID = @studentID AND archived IS NULLGO But when I applied the same function to the following stored procedure CREATE PROCEDURE proc_memberDetails@memberID int ASSELECT * FROM member WHERE id = @memberIDGO I received this message Exception Details: System.Data.SqlClient.SqlException: Procedure or function proc_memberDetails has too many arguments specified.Source Error: Line 33: SqlDataAdapter sqlDA = new SqlDataAdapter(); Line 34: sqlDA.SelectCommand = sqlComm; Line 35: sqlDA.Fill(dataSet); Line 36: Line 37: return dataSet; The function I am using is returning a DataSet as below: public DataSet ExecuteStoredProcSelect (string sqlProcedure, ArrayList paramName, ArrayList paramValue) { DataSet dataSet = new DataSet(); SqlConnection sqlConnect = new SqlConnection(GetDBConnectionString()); SqlCommand sqlComm = new SqlCommand (sqlProcedure, sqlConnect); sqlComm.CommandType = CommandType.StoredProcedure; for (int n=0; n<paramName.Count; n++) { sqlComm.Parameters.Add(paramName[n].ToString(),Convert.ToInt32(paramValue[n])); } SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = sqlComm; sqlDA.Fill(dataSet); return dataSet; } If this is not the correct way, is there any other way to write a function to return a dataset as the result of the stored procedure? Thanks.
View Replies !
Retrieve Data From A Stored Procedure
Hello Group I am new to stored procedures and I have been fighting this for a while and I hope someone can help. In my sp it checks username and password and returns an integer value. I would like to retrieve some data from the same database about the user (the users first and last name and the password of the user). I can’t retrieve the data. Here is my code. CREATE PROCEDURE stpMyAuthentication ( @fldUsername varchar( 50 ), @fldPassword Char( 25 )--, --@fldFirstName char( 30 ) OUTPUT, --@fldLastName char( 30 ) OUTPUT ) As DECLARE @actualPassword Char( 25 ) SELECT @actualPassword = fldPassword FROM tbMembUsers Where fldUsername = @fldUsername IF @actualPassword IS NOT NULL IF @fldPassword = @actualPassword RETURN 1 ELSE RETURN -2 ELSE RETURN -1 SELECT fldFirstName, fldLastName, fldPassword FROM tbMembUsers Where fldUsername = @fldUsername GO ############### login page ################ Sub Login_Click(ByVal s As Object, ByVal e As EventArgs) If IsValid Then If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False) End If End If End Sub Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer Dim myConn As SqlConnection Dim myCmd As SqlCommand Dim myReturn As SqlParameter Dim intResult As Integer Dim sqlConn As String Dim strFirstName, strLastName As String sqlConn = ConfigurationSettings.AppSettings("sqlConnStr") myConn = New SqlConnection(sqlConn) myCmd = New SqlCommand("stpMyAuthentication", myConn) myCmd.CommandType = CommandType.StoredProcedure myReturn = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int) myReturn.Direction = ParameterDirection.ReturnValue myCmd.Parameters.Add(Trim("@fldUsername"), Trim(strUsername)) myCmd.Parameters.Add(Trim("@fldPassword"), Trim(strPassword)) myCmd.Parameters.Add("@fldFirstName", strFirstName) myCmd.Parameters.Add("@fldLastName", strLastName) myCmd.Parameters.Add("@fldPassword", strPassword) myConn.Open() myCmd.ExecuteNonQuery() intResult = myCmd.Parameters("RETURN_VALUE").Value myConn.Close() 'If strPassword = 55555555 Then ' Session("intDefaultPass") = 1 'End If Session("strFullName") = strFirstName & " " & strLastName Session("strPassword") = strPassword If intResult < 0 Then If intResult = -1 Then lblMessage.Text = "Username Not Registered!<br><br>" Else lblMessage.Text = "Invalid Password!<br><br>" End If End If Return intResult End FunctionAt this time I am not getting any errors. How can I retrieve the data that I am after? Michael
View Replies !
Stored Procedure That Retrieve Object Name
Hi to all! Is there a system stored procedure that retrieve info or name about an object? I must know if a database contains a specific table and i must know if there is a specific DTS.. before execute it.. someone could help me?? thx a lot! Ale. Sorry for my poor english!! bye! =)
View Replies !
Stored Procedure To Retrieve UserId From Aspnet_Users
Im a bit confused on my programming skills. I know how to add a user and password using the Membership.CreateUser class. ' Define database connection Dim conn As New SqlConnection("Server=localhostSqlExpress;" & _ "Database=lnp;Integrated Security=True") ' Create command Dim comm As New SqlCommand( _ "SELECT oldLogId, oldPassword FROM userDetails WHERE (oldLogId = N'chuck') OR (oldLogId = N'top_dawg')", conn) ' Open connection conn.Open() ' Execute the command Dim reader As SqlDataReader = comm.ExecuteReader() ' Do something with the data While reader.Read() Dim partPassword = reader.Item("oldPassword") ' Adds underscore character and first 3 letters of password for distinct USER ID Dim newUserID = reader.Item("oldLogId") & "_" & partPassword.SubString(0, 3) ' Create User if Valid Try Membership.CreateUser(newUserID, partPassword) Catch ex As MembershipCreateUserException ' Code that is executed when an exception is generated ' The exception's details are accessible through the ex object usersLabel.Text &= "<p><strong>Exception: " & newUserID & "</strong><br />" & ex.ToString() & "<br /><strong>Message: </strong><br />" & ex.Message & "</p>" End Try End While usersLabel.Text &= "<p><strong>END OF RECORDS</strong></p>" ' Close the reader and the connection reader.Close() conn.Close() But now I want to do something in a Stored Procedure USE [lnp]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[sp_createPortingDetailsWithUsers]-- Add the parameters for the stored procedure here @passUserId NVarChar(50), @passPassword NVarChar(50)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;Membership.CreateUser(@passUserId, @passPassword)Select SCOPE_IDENTITY()ENDBut when I try to execute this in SQL SERVER MANAGEMENT STUDIO EXPRESS, I get an error near the line MEMBERSHIP.CREATEUSER(@passUserId,@passPassword)Msg 102, Level 15, State 1, Procedure sp_createPortingDetailsWithUsers, Line 11Incorrect syntax near 'Membership'. What I want to do is create a user with a UserID and Password that I am passing from a table and then retrieve the USERID (unique identifier) when I create the new user. Can anyone suggest or show a tutorial on what I am doing wrong? I appreciate any assistance in advance,Chuck
View Replies !
Retrieve Output Parameter From Stored Procedure
Hi, I'm having problems retrieving the output parameter from my stored procedure, i'm getting the following error An SqlParameter with ParameterName '@slideshowid' is not contained by this SqlParameterCollection code as follows: int publicationId = (int) Session["PublicationId"];string title = txtTitle.Text;int categoryid = Convert.ToInt32(dlCategory.SelectedItem.Value);SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);SqlCommand myCommand = new SqlCommand("sp_be_addSlideshow", myConnection);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add(new SqlParameter("@publicationid", SqlDbType.Int));myCommand.Parameters["@publicationid"].Value = publicationId;myCommand.Parameters.Add(new SqlParameter("@title", SqlDbType.NVarChar));myCommand.Parameters["@title"].Value = title;myCommand.Parameters.Add(new SqlParameter("@categoryid", SqlDbType.Int));myCommand.Parameters["@categoryid"].Value = categoryid;myConnection.Open();myCommand.ExecuteNonQuery();string slideshowId = myCommand.Parameters["@slideshowid"].Value.ToString();myConnection.Close(); my stored procedure: CREATE PROCEDURE sp_be_addSlideshow( @publicationid int, @title nvarchar(50), @categoryid int, @slideshowid int = NULL OUTPUT)AS INSERT INTO Slideshow(publicationid,title,slideshowcategoryid,deleted) VALUES (@publicationid,@title,@categoryid,0) SELECT @slideshowid = SCOPE_IDENTITY()GO
View Replies !
Retrieve Id Of Newly Added Row In Stored Procedure
Hi, I am trying to return the id of the newly added row after the insert statement in my stored procedure. I was told to "RETURN SCOPE_IDENTITY()", which i did. The problem is i do not know how to get this value in asp.net? I added this code below in my business layer. myDAL refers to an object of my DAL layer. In my DAL layer, i have a addPara() method which will help me dynamically add as many parameters. Someone please advise me on what to do. Thanks myDAL.addPara("@RETURN_VALUE", , SqlDbType.Int, , ParameterDirection.ReturnValue) Stored Procedure: CREATE PROCEDURE [ADDPROMOTION] ( @PROMOTIONNAME VARCHAR (100), @PROMOSTARTDATE DATETIME, @PROMOENDDATE DATETIME, @DISCOUNTRATE INT, @PROMODESC VARCHAR(100) ) As -- INSERT the new record INSERT INTO MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE, PROMOENDDATE, PROMODESC) VALUES (@PROMOTIONNAME, @DISCOUNTRATE, @PROMOSTARTDATE, @PROMOENDDATE, @PROMODESC) -- Now return the InventoryID of the newly inserted record RETURN SCOPE_IDENTITY() GO
View Replies !
How To Retrieve Large String From Stored Procedure?
Hello! This is my scenario... Development - Visual Studio 2005 Database - MS SQL 2005 I have written a stored procedure that has a output parameter whose type is NVARCHAR(MAX). Then I use C# to wrap this stored procedure in OLEDB way. That means the OleDbType for that parameter is VarWChar. This works fine if the string size is less than 4000. If more than 4000, the remaining part will be chopped off. The situation gets worst if I replace VarWChar with LongVarWChar. There is no error nor warning at compiling time. But there is an exception at run time. So... Does anyone here can help me out? Thanks in advance.
View Replies !
Want A Stored Procedure To Dynamically Select Views Pulled From A Table.
I am building a dashboard features that allows user to select reports from a dropdownlist. It is pulling from a table called Reports (cols: ReportID, Description, sqlView) In this Report table the report is associated to a view that queries the report. And the user's selections are stored in table called UserReport (cols: userID, ReportID, createDt) . I need to get a Dataset to contain datables of all reports selected. (for example a user select 3 reports, the dataset should contain 3 datables that represent the report). I want to accomplish this by create a store procedure that queries the Reports table and then dynamically executes the views that related to the user selected reports. Can anyone give me an example on how to create the storeprocedure? Thanks, CG
View Replies !
Retrieve Count From Stored Procedure And Display In Datagrid.
Hi Guys, I have a sql procedure that returns the following result when I execute it in query builder: CountE ProjStatus 6 In Progress 3 Complete 4 On Hold The stored procedure is as follow: SELECT COUNT(*) AS countE, ProjStatusFROM PROJ_ProjectsGROUP BY ProjStatus This is the result I want but when I try to output the result on my asp.net page I get the following error: DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Web.HttpException: DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.Source Error: Line 271: </asp:TemplateColumn> Line 272: <asp:TemplateColumn> Line 273: <itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate> Line 274: </asp:TemplateColumn> Line 275: </columns> My asp.net page is as follows: <script runat="server"> Dim myCommandPS As New SqlCommand("PROJ_GetProjStatus") ' Mark the Command as a SPROC myCommandPS.CommandType = CommandType.StoredProcedure Dim num as integer num = CInt(myCommand.ExecuteScalar) 'Set the datagrid's datasource to the DataSet and databind Dim myAdapterPS As New SqlDataAdapter(myCommandPS) Dim dsPS As New DataSet() myAdapter.Fill(dsPS) dgProjSumm.DataSource = dsPS dgProjSumm.DataBind() myConnection.Close() </script> <asp:datagrid id="dgProjSumm" runat="server" BorderWidth="0" Cellpadding="4" Cellspacing="0" Width="100%" Font-Names="Verdana,Arial,Helvetica; font-size: xx-small" Font-Size="xx-small" AutoGenerateColumns="false"> <columns> <asp:TemplateColumn HeaderText="Project Summary" HeaderStyle-Font-Bold="true" > <itemtemplate> <%# BgColor(DataBinder.Eval(Container.DataItem, "ProjStatus" ))%> </itemtemplate> </asp:TemplateColumn> <asp:TemplateColumn> <itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate> </asp:TemplateColumn> </columns> </asp:DataGrid> Please help if you can Im havin real trouble here. Cheers
View Replies !
Retrieve Multiple Variables From Stored Procedure (SQLHelper)
Hi all, I am using SQLHelper to run a Stored Procedure. The Stored Procedure returns 3 variables: ie: SELECT @Hits = COUNT(DISTINCT StatID) FROM Stats ...etc... The SP is currently called as below: SqlParameter[] sqlParams = new SqlParameter[] { new SqlParameter("@FromDate", Request["FromDate"]), new SqlParameter("@ToDate", Request["ToDate"]), }; My question is this: How do I retrieve these variables? I know I need to declare a new SqlParameter and change it's Direction to Output but I am unsure of the exact syntax required to do this. Thanks in advance, Pete
View Replies !
Problem To Retrieve Data From SQL Server 7.0 Via Stored Procedure
I'm writing VB 6.0 codes to retrieve data from SQL Server 7.0. if the sql code is embedded in VB, everything works fine. But when I tried to use stored procedure, one error called "invalid object name 'author'" occurs. author is a table in my database and obviously is in the database. what's wrong? appreciate for any suggestion! ...mike
View Replies !
Unable To Retrieve The @@Error Code In Stored Procedure
Hi All, In my application on click of Delete button, actually I am not deleting the record. I am just updating the flag. But before updating the record I just want to know the dependency of the record ie. if the record I am deleting(internally updating) exists any where in the dependent tables, it should warn the user with message that it is available in the child table, so it can not be deleted. I want to check the dependency dynamically. For that I have written the following procedure. But it is updating in both cases. CREATE proc SProc_DeleteRecord @TableName varchar(50), @DeleteCondition nVarchar(200) As Begin Begin Transaction Declare @DelString nvarchar(4000) set @DelString = 'Delete from ' + @TableName + ' where ' + @DeleteCondition execute sp_executeSql @DelString RollBack Transaction --because i donot want to delete the record in any case If @@Error <> 0 Begin select '1' End Else Begin Declare @SQLString nvarchar(4000) set @SQLString = 'Update ' + @TableName + ' set DeletedFlag=1 where ' + @DeleteCondition execute sp_executeSql @SQLString select '0' End End Thanks & Regards Bijay
View Replies !
How To Dynamically Assign Database Name In Query Or Store Procedure?
Hello, I am not sure if this possible, but I have store procedures that access to multiple databases, therefore I currently have to hardcode my database name in the queries. The problem start when I move my store procedures into the production, and the database name in production is different. I have to go through all my store procedures and rename the DBname. I am just wonder if there is way that I could define my database name as a global variable and then use that variable as my DB name instead of hardcode them? something like Declare @MyDatabaseName varchar(30) set @MyDatabaseName = "MyDB" SELECT * from MyDatabaseName.dbo.MyTable Any suggestion? Please. Thanks in advance
View Replies !
No Result From Stored Procedure
Hello evry1. i m new to SQL Server2000. plzz tell me where i m wrong in this SP . this procedure is not giving any error but it is not showing any result. plzz help me n give me any idea if u know how i can solve my problem best regards sadaf n here is the procedure CREATE PROCEDURE Search12 ( @signup char(50), @user_name1 [char](50), @gender1 [char](6), @place1 [char](100), @email_address1 [char](50), @relegion1 [char](50), @political_view1 [char](50), @passion1 [varchar](150), @sports1 [varchar](150), @activities1 [varchar](150), @books1 [varchar](150), @music1 [varchar](150), @tv_shows1 [varchar](150), @movies1 [varchar](150), @cuisines1 [varchar](150), @intrested_in1 [varchar](150), @education1 [char](100), @college_university1 [char](50), @occupation1 [char](50), @industry1 [char](50), @job_desc1 [char](50), @career_intrest1 [char](100), @latitude1 [decimal], @longitude1 [decimal]) AS if (@user_name1 is not null and len(@user_name1) > 0) begin select * from [profile] where [user_name] like '%@user_name1%' and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@gender1 is not null and len(@gender1) > 0) begin select * from [profile] where gender = @gender1 and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if(@email_address1 is not null and len(@email_address1) > 0) begin select * from [profile] where [email-address] like '%@email_address1%' and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if(@relegion1 is not null and len(@relegion1) > 0) begin select * from [profile] where relegion = @relegion1 and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@political_view1 is not null and len(@political_view1) > 0) begin select * from [profile] where political_view = @political_view1 and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@passion1 is not null and len(@passion1) > 0) begin select * from [profile] where passion like '%@passion1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if(@sports1 is not null and len(@sports1) > 0) begin select * from [profile] where sports like '%@sports1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@activities1 is not null and len(@activities1) > 0) begin select * from [profile] where activities like '%@activities%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@books1 is not null and len(@books1) > 0) begin select * from [profile] where books like '%books1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@music1 is not null and len(@music1) > 0) begin select * from [profile] where music like '%@music%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if(@tv_shows1 is not null and len(@tv_shows1) > 0) begin select * from [profile] where tv_shows like '%@tv_shows1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@movies1 is not null and len(@movies1) > 0) begin select * from [profile] where movies like '%@movies1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@cuisines1 is not null and len(@cuisines1) > 0) begin select * from [profile] where cuisines like '%@cuisines1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@intrested_in1 is not null and len(@intrested_in1) > 0) begin select * from [profile] where intrested_in = @intrested_in1 and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@education1 is not null and len(@education1) > 0) begin select * from [profile] where education like'%@education1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@college_university1 is not null and len(@college_university1) > 0) begin select * from [profile] where institution like '%@college_university1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@occupation1 is not null and len(@occupation1) > 0) begin select * from [profile] where occupation like '%@occupation1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@industry1 is not null and len(@industry1) > 0) begin select * from [profile] where industry like '%@industry1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@job_desc1 is not null and len(@job_desc1) > 0) begin select * from [profile] where job_desc like '%@job_desc1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@career_intrest1 is not null and len(@career_intrest1) > 0) begin select * from [profile] where career_intrest like '%@career_intrest1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end GO
View Replies !
Seeing Result Of Stored Procedure
i made a new stored procedure and execute it. it works great but where can i see the result ? i want to see the number of Count(*) in a window inside the SQL Server Managment studio, can i ? my stored procedure is as follows : set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[GetTotalMovieCreations] AS select count(*) from tbl_Animations where statusID = 1 and ToolID = 1 and ToolID = -1 and isDeleted = 0 thanks...
View Replies !
Best Way To Send Email From A Stored Procedure (dynamically Changing Paramenters And Attachment)
Hello everyone,I need advice of how to accomplish the following:Loop though records in a table and send an email per record. Emailrecipient, message text and attachment file name - that's all changesrecord by record.Is it doable from a stored procedure (easily I mean, or am I better offwriting a VB app)? There are so many options of sending mail from SQLserver - CDONTS, SQL MAIL TASK, xp_sendmail. What's easier to implementand set up?Thanks a lot!!!(links and fragments of sample code would be greatlyappreciated)Larisa
View Replies !
Retrieve An Output Parameter In Stored Procedure From Java Application
in my java application I've made a call to this stored procedureCREATE procedure pruebaICM@pANI varchar(20),@pTABLA varchar(20),@pInsert varchar(500),@pUpdate varchar(1000),@pFLAG varchar(1),@pResultado int OUTPUTasbeginDECLARE @ani varchar(20)declare @cliente intDECLARE @sentencia nvarchar(1000)DECLARE @tabla nvarchar(20)DECLARE @sentencia_where nvarchar(50)DECLARE @sql nvarchar(1050)SET NOCOUNT ONset @tabla = @pTABLAset @ani = @pANISELECT @sql= N'select @cliente=count(ani) from '+ @tabla + N' whereani = ' + @aniexec sp_executesql @sql, N'@Cliente INT OUTPUT', @Cliente OUTPUTSELECT @Clienteif (@pFLAG = 'A') or (@pFLAG = 'Actualizar') or (@pFLAG = 'I')beginif (@cliente = 0)beginset @sentencia = N'insert into ' +@pTABLA + N' values (' + @pInsert + N')'EXEC sp_executesql @sentenciaset @pResultado = 1SELECT @pResultadoreturn @pResultadoendif (@cliente = 1)beginset @sentencia = N'update ' + @pTABLA +N' set ' + @pUpdateset @sentencia_where = N' where ANI =' + @pANIset @sql = @sentencia +@sentencia_whereEXEC sp_executesql @sqlset @pResultado = 2SELECT @pResultadoreturn @pResultadoendendelse if (@pFLAG = 'B') or (@pFLAG = 'Borrar')beginif (@cliente = 0)beginset @pResultado = 0SELECT @pResultadoreturn @pResultadoendelse if (@cliente = 1)beginset @sentencia = N'delete from '+@pTABLA + N' where ANI = ' + @pANIEXEC sp_executesql @sentenciaset @pResultado = 3SELECT @pResultadoreturn @pResultadoendendEXEC sp_cursorcloseendMy problem is that the ouutput param @pResultado haven't got any valueand don't return anything to the java application. How can I fix thisproblem?Thanka very much for helping me!!!!
View Replies !
Two Result Sets From Stored Procedure..
My stored procedure displays two result sets. How can i use that result sets in my 3-tier application. I want to bind first resultset to repeater control and second to label control. I am using SqlDataReader...
View Replies !
Strange Stored Procedure Result
Hello all, I have the following Stored Procedure that has been working perfectly for the last year: CODE ==================================================== DELETE FROM tblReportMainMembers INSERT INTO tblReportMainMembers (emplid, userid, membership, price, approvecode, purchasedate, wpecend)SELECT DISTINCT tblCart.emplid, tblCart.userid, tblCart.membership, tblCart.Price, tblcart.approvecode, tblCart.addedcart, tblCart.addedcart + 365FROM tblCart INNER JOIN tblMemberships ON tblCart.membership = tblMemberships.idWHERE (tblMemberships.type = 'MAIN') AND approvecode IS NOT NULL AND approvecode <> 'X44444444444' UPDATE tblReportMainMembersSET tblReportMainMembers.fname = tblRecords.fname, tblReportMainMembers.lname = tblRecords.lname, --tblReportMainMembers.userid = tblRecords.id, tblReportMainMembers.address = tblRecords.home_address, tblReportMainMembers.city = tblRecords.city, tblReportMainMembers.state = tblRecords.state, tblReportMainMembers.zip = tblRecords.zip, tblReportMainMembers.homephone = tblRecords.home_phone, tblReportMainMembers.officephone = tblRecords.office_phone, tblReportMainMembers.email = tblRecords.email, tblReportMainMembers.signup = tblRecords.signupFROM tblRecordsWHERE tblReportMainMembers.emplid = tblRecords.emplid UPDATE tblReportMainMembersSET tblReportMainMembers.membership = tblMemberships.membershipFROM tblMembershipsWHERE tblReportMainMembers.membership = tblMemberships.id UPDATE tblReportMainMembersSET tblReportMainMembers.emplid = Onecard.dbo.Accounts.CustomFROM Onecard.dbo.AccountsWHERE tblReportMainMembers.emplid = Onecard.dbo.Accounts.Account SELECT RTRIM(emplid) AS EMPLID, RTRIM(userid) AS USERID, RTRIM(fname) AS FNAME, RTRIM(lname) AS LNAME, RTRIM(membership) AS MEMBERSHIP, CAST(price AS varchar(12)) AS PRICE, RTRIM(approvecode) AS APPROVECODE, CONVERT(varchar(20), purchasedate, 101) AS PURCHASEDATE, CONVERT(varchar(20), wpecend, 101) AS WPECEND, RTRIM(address) AS ADDRESS, RTRIM(city) AS CITY, RTRIM(state) AS STATE, RTRIM(zip) AS ZIP, RTRIM(homephone) AS HOMEPHONE, RTRIM(officephone) AS OFFICEPHONE, RTRIM(email) AS EMAIL, signup AS SIGNUP FROM tblReportMainMembersWHERE fname IS NOT NULL AND lname IS NOT NULLORDER BY lname As you can tell from the procedure, i copy some records into a report table, do some modifications, and then send the results to the browser. But all of a sudden, i'm getting timeouts on all my users. But here is the strange part, when i take the above code and run it using Query Analyzer, it works. And then after that, my users are OK running the clients for about 1 week. And then it starts acting up again. Everytime i run the code in Query Analyzer, i have no more problems for about a week. Weird isn't it. Any ideas? Thanks in advance.Richard M.
View Replies !
Passing A Result Set To A Stored Procedure
Hi All,I have sometimes used the following sort of query to pull data from one table to another:INSERT INTO Table1 SELECT fname, lname FROM Table2Now, let's suppose that I had created a stored procedure to do the insert (and any other logic i was concerned about) and I did something like this:EXECUTE Table1 _Insert SELECT fname, lname FROM Table2It won't work, giving an error that looks something like this:Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0Procedure 'Table1_Insert' expects parameter '@fname', which was not supplied.I assume I'm not doing things right... how would I pass a result set to a stored procedure, with each row corresponding to an input parameter of the stored procedure?
View Replies !
Manipulating The Result Set Of One Stored Procedure From Another....
Hi,I have one stored procedure that calls another ( EXEC proc_abcd ). I wouldlike to return a result set (a temporary table I have created in theprocedure proc_abcd) to the calling procedure for further manipulation. Howcan I do this given that TABLE variables cannot be passed into, or returnedfrom, a stored procedure?Thanks,RobinExample: (if such a thing were possible):DECLARE @myTempTable1 TABLE ( ID INT NOT NULL )DECLARE @myTempTable2 TABLE ( ID INT NOT NULL )...../*Insert a test value into the first temporary table*/INSERT INTO @myTempTable1 VALUES ( 1234 )...../*Execute a stored procedure returning another temporary table ofvalues.*/EXEC proc_abcd @myTempTable2 OUTPUT......../*Insert the values from the second temporary table into the first.*/SELECT * INTO @myTempTable1 FROM @myTempTable2
View Replies !
How Do I See The Result Of Calling A Stored Procedure In VB6 ?
HelloI'm calling a stored procedure in VB6 using the "execute" command ofrdoquery.Does anyone know how to check whether or not the execute command wassuccesfull or not. Not on the level of whether any rows were updated orselected but whether any Sql errors occured. VB picks up on any errorsbut I want to be able to identify them on the application level and dealwith them myself.I already tried the "rowsaffected" property but that isn't what I need.Any ideas anyone ?David Greenberg
View Replies !
Executing A Stored Procedure Result
Hi Guys.. How Can i Execute a result from a StoredProcedure... I got a sp that generates drop index and pk from all tables in the DB.. I got this results from running (sp_dropallindex) like this: ALTER TABLE Table1 DROP CONSTRAINT PK_Table1 GO ALTER TABLE Table2 DROP CONSTRAINT PK_table2 GO DROP INDEX table1.index1 GO DROP INDEX table1.index2 GO I need to execute that result.. I know that i can copy/paste into Query Analyzer and then run it but how can i handle that result and run all in shot ... I tried something like this: DECLARE @DROP AS VARCHAR(8000) SET @DROP='exec sp_drop_allindex' EXECUTE (@DROP) and i see the same out , but my indexes and PK still there ... i'm confused about it .. PLEASE HELP ME OUT :D
View Replies !
SQL Stored Procedure Not Returning Result Set
This is a strange issue I'm encountering. I have a websphere application that calls a basic Stored Procedure in SQL Server 2000. The SP works fine. Now, if I create a TEMP table in the SP, I no longer get a resultset. No results are returned by the websphere. Even if I do not use the temp table... that is, I just create the temp table like so: SELECT region_code, OperGroup_Code, country_name, MajorGroup_Name, RptgEntity_Name, shell_code, CRC, ' ' as right_type INTO #tmpTShell FROM TShell WHERE 1=0 But I then grab my records using the query that works (querying another table), I still get NULL for records returned. Only when I comment out the above TEMP table creation do I get the results back in websphere. Another strange thing is that this works fin in SQL analyzer. WRegardless of whether I have the temp table creation commented out or not, it always works in the Query Analyzer whe I call the SP. It just seems to effect WebSphere or my java code in that it returns null if I create the temp table. Has anyone ever experienced anthying like this? Any help would be greatly appreciated! Thanks.
View Replies !
Dealing With A Result Set From A Stored Procedure
Hi, I have a problem with dealing with result sets returned from stored procedures. I have a procedure like: CREATE PROCEDURE SampleProcedure AS BEGIN SELECT * FROM SampleTable END GO By executing this stored porocedure is returned result set containing data from SampleTable table. (EXECUTE SampleProcedure) The returned resultset can be seen in Query Analyzer and can be handled from ADO.NET without any hesitate. But I can't use this result set from other stored procedure. I tried: SELECT * FROM (EXEC SampleProcedure) But there is sintax error in select statement. Does anybody know, how to store the result set into a teporary table or select it by SELECT statement? Thanks. MarF.
View Replies !
Help...i Need To Store The Result Of A Stored Procedure...
Hi guys. I have been struggling for days now to store the result of a stored procedure from a linkedserver. To make a long story short, here is my code... CREATE PROCEDURE F_GET_KRONOS_HRS @wono varchar(12) AS BEGIN declare @str nvarchar(2000) declare @a varchar(10) select @str = 'select * from openquery(kronos," SELECT decode(a.PAYCATID,1,'+ "'ST'"+",'OT'"+') paycode ,f_calc_hrs(sum(a.AMOUNT)) hrs ' select @str = @str + 'FROM TOTALEDPAYCATEDIT a ,LABORACCT b ,LABORLEVELENTRY c ' select @str = @str + 'where ( c.NAME =' + "'"+'' + @wono +"') " select @str = @str + 'and (a.LABORACCTID = b.LABORACCTID) ' select @str = @str + 'and c.LABORLEVELENTRYID = b.LABORLEV3ID group by a.PAYCATID ' + '' + '")' /* exec sp_executesql @str */ create table #t (paycode varchar(7), hrs varchar(255)) insert into #t exec sp_executesql @str select @a = hrs from #t where paycode='ST' drop table #t print @a if i call exec sp_execute @str, it will output this PAYCODE HRS ------- -------- ST 08: 30 OT 54: 00 (2 row(s) affected) I want those #'s store into a temp variable OR be passed to another procedure. HOW DO I DO IT. This is the last step holding me back from completing my DataWareHouse. Thanks
View Replies !
Returning More Than One Result Set From A Stored Procedure
Hi, I am wondering wether it is possible to return more than one result set to my application from a stored procedure. My sp is below, but all that returns to my application is the result of the first iteration of the invoice_cursor. Any help or alternate methods to achieve this would be appreciated. Code: CREATE PROCEDURE procBillingSummaryCastingDollars( @startDate Varchar(10),@endDate Varchar(10)) AS DECLARE @invoice_id varchar(11); DECLARE invoice_cursor CURSOR FOR SELECT invoice_id FROM receivable_dist rd WHERE rd.POSTING_DATE BETWEEN @startDate AND @endDate AND (rd.GL_ACCOUNT_ID like '%3000%' OR rd.GL_ACCOUNT_ID like '%3101%') order by invoice_id OPEN invoice_cursor FETCH NEXT FROM invoice_cursor INTO @invoice_id WHILE @@FETCH_STATUS = 0 BEGIN -- Declare an inner cursor based -- on invoice_id from the outer cursor. IF charindex('C',@invoice_id) = 0 AND charindex('M',@invoice_id) = 0 BEGIN SELECT ss.metal_code, ss.equip_code, ss.core, CASE r.status WHEN 'X' THEN rl.amount*-1 ELSE rl.amount END as CastingDollars FROM receivable r INNER JOIN receivable_line rl ON r.invoice_id = rl.invoice_id INNER JOIN cust_order_line cl ON cl.cust_order_id = rl.cust_order_id INNER JOIN salesum_partid ss ON ss.partid = cl.part_id WHERE rl.invoice_id = @invoice_id END ELSE BEGIN SELECT ss.metal_code, ss.equip_code, ss.core, CASE r.status WHEN 'X' THEN rl.amount*-1 ELSE rl.amount END as CastingDollars FROM receivable r INNER JOIN receivable_line rl ON r.invoice_id = rl.invoice_id INNER JOIN salesum_partid ss ON substring(ltrim(rl.reference),1,(patindex('%-_-____%',ltrim(rl.reference))+6)) = ss.partid WHERE r.invoice_id = @invoice_id END FETCH NEXT FROM invoice_cursor INTO @invoice_id END CLOSE invoice_cursor DEALLOCATE invoice_cursor GO
View Replies !
|
TRACKER
