Here is my problem: I have a strored procedure that inserts some records
into one table and then selects some records from another table at the end.
The stored procedure takes several parameters, first one of them is marked as
OUTPUT. I'm using it to return an id of the inserted record. The procedure is called from asp.net code with first parameter set as ParameterDirection.InputOutput (tried with just Output as well). Now for the problem: if the the select statement at the end returns 0 records everything works and i my first parameter contains the @@IDENTITY value from the insert statement like it is supposed to.
If the select statement at the end returns 1 or more records my output parameter is not updated at all and contains the same value as before the procedure was run. All the records are inserted correctly.
if i try to return the @@identity as a plain select statement instead of through the parameter
i get System.DBNull.
I hope you can shed some light on this for me. Here is my stored procedure:
CREATE PROCEDURE cwSaveProductInquiry
@inquiryId int OUTPUT,
@libraryName nvarchar(500),
@contactName nvarchar(200),
@address nvarchar(100),
@city nvarchar(50),
@state nvarchar(3),
@zip nvarchar(10),
@phone nvarchar(50),
@email nvarchar(100),
@comment nvarchar(3000),
@productIds nvarchar(2000)
AS
INSERT INTO INQUIRY (LibraryName, ContactName, Address, City, State, Zip, Phone, Email, Comment) VALUES(@libraryName, @contactName, @address, @city, @state, @zip, @phone, @email,@comment)
--i tried including this statement at the end as well but that did not do the
--trick either
select @inquiryId=@@IDENTITY FROM INQUIRY
set nocount on
declare @separator_position int -- This is used to locate each separator character
declare @objectId varchar(200) -- this holds each array value as it is returned
The dynamic SQL statements with output parameters, unfortunately, in the documentation are not described. À) Simple example of the dynamic SQL statement with output parameters
-- dynamic SQL statements expects parameter of type 'ntext/nchar/nvarchar'. declare @SQLString nvarchar(4000), @ParmDefinition nvarchar(4000) -- the third parameter passed in the dynamic statement as by output, returns the length of -- the hypotenuses of a right triangle, two first parameters are lengths of legs of a triangle declare @nHypotenuse float select @SQLString = 'select @nHypotenuse = sqrt(square(@nLeg1_of_a_triangle)+square(@nLeg2_of_a_triangle))', @ParmDefinition = '@nLeg1_of_a_triangle float, @nLeg2_of_a_triangle float, @nHypotenuse float out' -- we call the dynamic statement in such a way exec sp_executesql @SQLString, @ParmDefinition, @nLeg1_of_a_triangle = 3.0, @nLeg2_of_a_triangle = 4.0, @nHypotenuse = @nHypotenuse out -- or in such a way exec sp_executesql @SQLString, @ParmDefinition, 3.0, 4.0, @nHypotenuse out select @nHypotenuse -- Displays 5.0
B) Example of usage of the dynamic statement with output parameter and the function GETALLWORDS. The following stored procedure get all words from a field of the type text or ntext, the word length should not exceed 4000 characters.
CREATE PROCEDURE SP_GETALLWORDSFROMTEXT @TableName sysname, @FieldIdName sysname, @FieldIdValue sql_variant, @FieldTextName sysname, @cDelimiters nvarchar(256) = NULL AS -- this Stored procedure inserts the words from a text field into the table. -- WORDNUM int – Sequence number of a word -- WORD nvarchar(4000) – the word -- STARTOFWORD int – position in the text field, with which the word starts -- LENGTHOFWORD smallint – length of the word -- Parameters -- @TableName name of the table with the text or ntext field -- @FieldIdName name of Id field -- @FieldIdValue value of Id field -- @FieldTextName name of field text or ntext -- @cDelimiters Specifies one or more optional characters used to separate words in the text field begin set nocount on
declare @k int, @wordcount int, @nBegSubString int, @nEndSubString int, @nEndString int, @divisor tinyint, @flag bit, @RetTable bit, @cString nvarchar(4000), @TypeField varchar(13), @SQLString nvarchar(4000), @ParmDefinition nvarchar(500), @nBegSubString1 smallint, @nEndSubString1 smallint select @TableName = object_name(object_id(lower(ltrim(rtrim(@TableName))))), @FieldIdName = lower(ltrim(rtrim(@FieldIdName))), @FieldTextName = lower(ltrim(rtrim(@FieldTextName))), @cDelimiters = isnull(@cDelimiters, nchar(32)+nchar(9)+nchar(10)+nchar(13)), -- if no break string is specified, the function uses spaces, tabs, carriage return and line feed to delimit words. @nBegSubString = 1, @nEndSubString = 4000, @flag = 0, @RetTable = 0, @wordcount = 0
-- If the temporary table is not created in the calling procedure, we create the temporary table if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is null begin create table #GETALLWORDSFROMTEXT (WORDNUM int, WORD nvarchar(4000), STARTOFWORD int, LENGTHOFWORD smallint) select @RetTable = 1 end -- we use the dynamic SQL statement to receive the exact name of text field -- as we can write names of fields by a call of the given stored procedure in the arbitrary register -- in the string of parameters definition @ParmDefinition we use a keyword output -- and by a call of the dynamic SQL statement exec sp_executesql @SQLString, @ParmDefinition, @FieldTextName = @FieldTextName output -- Also we use a keyword output for definite before parameter select @SQLString = 'select @FieldTextName = name from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldTextName+'''' select @ParmDefinition = '@FieldTextName sysname output' exec sp_executesql @SQLString, @ParmDefinition, @FieldTextName = @FieldTextName output
-- we use the dynamic SQL statement to receive the exact name of Id field select @SQLString = 'select @FieldIdName = name from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldIdName+'''' select @ParmDefinition = '@FieldIdName sysname output' exec sp_executesql @SQLString, @ParmDefinition, @FieldIdName = @FieldIdName output
-- we use the dynamic SQL statement to receive the type of field (text or ntext) select @SQLString = 'select @TypeField = name from systypes where xtype = any ( select xtype from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldTextName+''')' select @ParmDefinition = '@TypeField varchar(13) output' exec sp_executesql @SQLString, @ParmDefinition, @TypeField = @TypeField output
select @divisor = case @TypeField when 'ntext' then 2 else 1 end -- 2 for unicode
-- we use the dynamic SQL statement to receive a length of the text field select @SQLString = 'select @nEndString = 1 + datalength('+ @FieldTextName+')/'+cast( @divisor as nchar(1)) +' from '+@TableName +' where '+ @FieldIdName+' = ' +cast(@FieldIdValue as nchar(50)) select @ParmDefinition = '@nEndString int output' exec sp_executesql @SQLString, @ParmDefinition, @nEndString = @nEndString output
-- We cut the text field into substrings of length no more than 4000 characters and we work with substrings in cycle while 1 > 0 begin -- we use the dynamic SQL statement to receive a substring of a type nvarchar(4000) from text field select @SQLString = 'select @cString = substring('+ @FieldTextName+','+cast( @nBegSubString as nvarchar(20)) +',' + cast( @nEndSubString - @nBegSubString + 1 as nvarchar(20))+') from '+@TableName +' where '+ @FieldIdName+' = ' +cast(@FieldIdValue as nchar(50)) select @ParmDefinition = '@cString nvarchar(4000) output' exec sp_executesql @SQLString, @ParmDefinition, @cString = @cString output
while charindex(substring(@cString, @nBegSubString1, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) > 0 and @nEndSubString >=@nBegSubString -- skip the character not in word, if any select @nBegSubString = @nBegSubString + 1 , @nBegSubString1 = @nBegSubString1 + 1
while charindex(substring(@cString, @nEndSubString1, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) = 0 and @nEndSubString >=@nBegSubString -- skip the character in word, if any select @nEndSubString = @nEndSubString - 1, @nEndSubString1 = @nEndSubString1 - 1
if @nEndSubString >=@nBegSubString begin select top 1 @wordcount = WORDNUM from #GETALLWORDSFROMTEXT order by WORDNUM desc select @cString = substring(@cString, @nBegSubString1, @nEndSubString1-@nBegSubString1+1) -- we use a function GETALLWORDS which one works with strings of a type nvarchar(4000) -- we add outcome result in the temporary table insert into #GETALLWORDSFROMTEXT (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD) select (@wordcount+WORDNUM), WORD, (@nBegSubString+STARTOFWORD-1), LENGTHOFWORD from dbo.GETALLWORDS(@cString, @cDelimiters)
select @nBegSubString = @nEndSubString + 1, @nEndSubString = @nEndSubString + 4000 end else select @nEndSubString = @nEndSubString + 4000 -- In a case if the substring consists of one delimiter
if @flag = 1 break if @nEndString <= @nEndSubString select @flag = 1, @nEndSubString = @nEndString end
-- If in a calling procedure the table was not created, we show the result if @RetTable = 1 select * from #GETALLWORDSFROMTEXT
end GO
Example of the call Stored procedure SP_GETALLWORDSFROMTEXT
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is not null drop table #GETALLWORDSFROMTEXT create table #GETALLWORDSFROMTEXT (WORDNUM int, WORD nvarchar(4000), STARTOFWORD int, LENGTHOFWORD smallint) exec dbo.SP_GETALLWORDSFROMTEXT 'Your Table name', 'Your Id field name', Value of Id field, ' text or ntext field name', @cDelimiters
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is not null select * from #GETALLWORDSFROMTEXT
-- GETALLWORDS() User-Defined Function Inserts the words from a string into the table. -- GETALLWORDS(@cString[, @cDelimiters]) -- Parameters -- @cString nvarchar(4000) - Specifies the string whose words will be inserted into the table @GETALLWORDS. -- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString. -- The default delimiters are space, tab, carriage return, and line feed. Note that GETALLWORDS( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter. -- Return Value table -- Remarks GETALLWORDS() by default assumes that words are delimited by spaces or tabs. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character. -- Example -- declare @cString nvarchar(4000) -- set @cString = 'The default delimiters are space, tab, carriage return, and line feed. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.' -- select * from dbo.GETALLWORDS(@cString, default) -- select * from dbo.GETALLWORDS(@cString, ' ,.') -- See Also GETWORDNUM() , GETWORDCOUNT() User-Defined Functions CREATE function GETALLWORDS (@cString nvarchar(4000), @cDelimiters nvarchar(256)) returns @GETALLWORDS table (WORDNUM smallint, WORD nvarchar(4000), STARTOFWORD smallint, LENGTHOFWORD smallint) begin declare @k smallint, @wordcount smallint, @nEndString smallint, @BegOfWord smallint, @flag bit
select @k = 1, @wordcount = 1, @BegOfWord = 1, @flag = 0, @cString = isnull(@cString, ''), @cDelimiters = isnull(@cDelimiters, nchar(32)+nchar(9)+nchar(10)+nchar(13)), -- if no break string is specified, the function uses spaces, tabs, carriage return and line feed to delimit words. @nEndString = 1 + datalength(@cString) /(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
while 1 > 0 begin if @k - @BegOfWord > 0 begin insert into @GETALLWORDS (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD) values( @wordcount, substring(@cString, @BegOfWord, @k-@BegOfWord), @BegOfWord, @k-@BegOfWord ) -- previous word select @wordcount = @wordcount + 1, @BegOfWord = @k end if @flag = 1 break
while charindex(substring(@cString, @k, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) > 0 and @nEndString > @k -- skip break characters, if any select @k = @k + 1, @BegOfWord = @BegOfWord + 1 while charindex(substring(@cString, @k, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) = 0 and @nEndString > @k -- skip the character in the word select @k = @k + 1 if @k >= @nEndString select @flag = 1 end return end
For more information about string UDFs Transact-SQL please visit the
If I have ad hoc SQL statements created by users, which could be parameterized, how could I derive the parmeters at runtime. I cannot use CommandBuilder.DeriveParameters() as that is for StoredProcedures only.
Just use Split on the SQL string? Or is there a better way, such as a third-party .Net Component?
I have setup a Database Audit Specification as follows:
Audit Action Type: SELECT | Object Class: DATABASE | Object Name: SHOPDB | Principal Name: public
Now, when I perform a SELECT query with a bound parameter such as:
SELECT * FROM myTable WHERE name='queryname'
What I see through the Audit Logs is something like:
SELECT * FROM myTable WHERE name='@1'
I understand that it is by design that we cannot see these parameters throught Database Level Auditing. I would like to know whether it is possible to see these parameters by any other means using
(1) SQL Server Enterprise Edition, (2) SQL Server Standard Edition, or (3) by an external tool.
I have a user login scenario where I would like to make sure that they not only exist in the user table, but also make sure there account is "verified" and "active". I'm trying to return 3 output parameters. UserID, verified, active. Is this possible?
Do I need just a select statement to do this? What is the difference between the output and select statements?
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
Hi all, From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
i have a form where filter information is going to be keyed/selected. the data from that form is copied into a struct regardless of whether each field has data in it or not (eg firstname may have been entered but not lastname, so the struct would contain firstname = 'john' and lastname = null) i then want to build an sql statement (not using stored procedures but just direct sql) from this struct data but obvioulsy can't just have a statement that says 'select ID from theTable where firstname='john' AND lastname=''" as this won't bring back the right info, if anything at all.
i normally build the statements like this
Code Block string sql = "select ID from MembersTemp where FirstName = '{0}'"; SqlCommand cmd = new SqlCommand(string.Format(sql, FirstName), _con); _con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); List<int> result = new List<int>(); while (rdr.Read()) { etc....
so really, what's the best way of building an sql statement where some search parameters won't hold a value and therefore need ignoring.
also, whilst i'm here, i use viz studio 2005 and sql server 2005 (express??), so that does mean i'm using TSQL as well??
Hi everyone, can anyone help me please I have a stored procedure that works quite well and returns one of the following codes, 1 2 3 or 4 which is dependant on business criteria. This sp works well and satsisfies all the criteria etc. I am calling this SP in asp.net (c#) like this using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["blah"].ConnectionString)) { int? intSQLCheck = null; con.Open(); SqlCommand cmd = new SqlCommand("check_values", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@param_clothing_id", this.DropDownList2.SelectedValue.ToString())); cmd.Parameters.Add(new SqlParameter("@empNo", this.DropDownList1.SelectedValue.ToString())); cmd.Parameters.Add(new SqlParameter("@createdBy", this.txtEmpNo.Text.ToString())); cmd.Parameters.Add(new SqlParameter("@price", this.txtPrice.Text)); cmd.Parameters.Add(new SqlParameter("@qty", this.txtQty.Text)); //cmd.Parameters.Add(new SqlParameter("@returnValue", 0)); cmd.Parameters.Add(new SqlParameter("@returnVal", 99)); cmd.Parameters["@returnVal"].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery();
What I would like to do know is use a variable intSqlCheck to hold the return value from the SP (and then do some processing of business logic using the variable) Any ideas anyone please
Hi, I have a form with a detailsview and a sqldatasource. The sqldatasource uses a stored procedure as the select command. This stored procedure requires 2 input parameters and returns 3 output parameters. A button on the form is enabled or disabled depending on the value of the output parameters. When the page loads, I get a "Object reference not set to an instance of an object" message, caused by the null value of an output parameter. SQL Profiler shows that the input parameters are correct, and if I run the stored procedure directly, it's okay. Can anybody tell me why I wouldn't get the output parameters? Thanks,Neil
I am new to SQL and trying to work out how to do this
i have a table called projectAllocationLog with 4 fields, there can be many users of any particular project, ie ID,projectID, User, Date 4 5000, 6, 10/02/2007 3 5000, 12, 08/07/2005 2 5004, 13, 08/05/2003 1 5000, 14, 04/05/2001
I want to write a stored proceedure that I can pass in the projectID, and return the most current user, this will always be the user with the highest ID for the project
ie for project 5000 it will be user 6
I was thinking along the lines of for each project select the top 1 from the table. where I can pass in the projectID, and pass out the ID field of the table. i am just not sure how to start this.
I am trying to select multiple values from the row with the max id,and output them within my stored procedure. How would I combine thefollowing two rows so that I only need to use "from mytable whereIDnum = (select (max(IDnum)) from mytable))" once. I have at least 8other outputs I will be setting and would like this to work moreefficiently than setting each individually... if possible.set @outmtd = (select outmtd from mytable where IDnum = (select(max(IDnum)) from mytable))set @outytd = (select outytd from mytable where IDnum = (select(max(IDnum)) from mytable))Thanks for any help you can giveMatt
Hello, i got this working using vb.net but becuase I'm learning asp.net I thought it would be a ggood idea to replicate the same function using C# as well1
The problem is I receive an output parameter through a stored procedure which is the users id.
I want to add this to a cookie as I know I'll be using it again and again, but the error I keep getting is cannot cxonvert type 'string' to type 'int'.
I've tried using conversion functions to make the value returned into an int value but I had no success!
I did this -
int id = (int)mycommand.Parameters["@id"].Value;
and then try to save that to the cookie but the error keeps appearing, I've also tried using the parse and ToInt32 function but the same/similar problems - any ideas?
Hi all,Is there a data transfer difference between returning a datareader that only has one row or using output parameters? For instance, I have a login page that shows the last login, member since, display level, number of file views, etc. Currently I am returning a datareader that reads one row and assigns this data to labels. But would it be quicker to use OUTPUT parameters instead of using a dataread and just get the values from the command object?ThanksJosh
When I call a proc with one input parameter and two output parameters, (all the three parameters having defaults in the proc), I was expecting to see these values defaulted to in the proc. But apparently, this not the case. Could someone tell me what am I doing wrong here? Appreciate your time.
USE Pubs GO
CREATE PROCEDURE dbo.MyTestProc (@InputParamVARCHAR(30) = 'Input', @OutPutParam1VARCHAR(30) = 'OutputParam1' OUTPUT, @OutPutParam2VARCHAR(30) = 'OuputParam2' OUTPUT) AS BEGIN SELECT @InputParam, @OutPutParam1, @OutPutParam2 END GO
Hi can any one explain why when I specify an outparameter as a string I only get back the first letter of the string when I call the procedure from .net?
DECLARE @tmpGUID as uniqueidentifier /*stores the temp guid from the function */ DECLARE @ReturnDomainName as varchar(32) /*stores the tmp domainname of the user who has the PACS name */
SET @Message=CAST('' as varchar(32)) SET @MessageNumber= 0
/* first check if PACS user name already exists or not */ SET @tmpGUID = dbo.fncFindPACSName(@UserPACSName)
IF @tmpGUID IS NOT NULL /* if the value is null the the PACSusername doesn't exist if its not null then does the current user already own that PACSUserName? */ BEGIN
/*if the users ids are different then another user already has the PACS name */ IF @tmpGUID<>@UserGUID BEGIN SELECT @ReturnDomainName = UserDomainName FROM tblUsers WHERE UserGUID=@tmpGUID SET @Message=@ReturnDomainName SET @MessageNumber = 1 RETURN
END END
/*now check the PAS name to see if it exists or is already in use by someone else first check if PACS user name already exists or not */ SET @tmpGUID = dbo.fncFindPASName(@PASName)
IF @tmpGUID IS NOT NULL /* if the value is null the the PACSusername doesn't exist if its not null then does the current user already own that PACSUserName? */ BEGIN
/*if the users ids are different then another user already has the PACS name */ IF @tmpGUID<>@UserGUID BEGIN SELECT @ReturnDomainName = UserDomainName FROM tblUsers WHERE UserGUID=@tmpGUID SET @Message=@ReturnDomainName SET @MessageNumber = 2 RETURN
END END
UPDATE tblUsers SET UserPACSName=@UserPACSName,UserPACSPassword=@UserPACSPassword, UserPACSPasswordIV=@UserPACSPasswordIV, PASName=@PASName, UserFriendlyName=@UserFriendlyName
WHERE UserGUID=@UserGUID
SET @Message=CAST('' as varchar(32)) SET @MessageNumber=3
RETURN
I call this proc via a vb.net program using a command.executenonquery and then look at the parameter.
For some reason I only get the first letter of the string back! i.e. if the string is LSDNET/nik I get 'L'.
If I run the proc from visual studio step in to procecure it works fine!
the vb I'm using is... (the createaddparameter simply creates a bunch of parameters to add to a command -with the specified names and dbtypes).
I'm obviously missing a trick but what it is I can't see. I seem to be able to pass all other data types back easily!
Dim LoadUserCommand As New SqlCommand
'create add command Dim CreateAddParameter() As String = {"@UserGUID", _ "@UserPACSName", "@PASName", "@UserPACSPassword", _ "@UserPACSPasswordIV", "@UserFriendlyName", "@Message", "@MessageNumber"} Dim UpdateDBTypes() As DbType = {DbType.Guid, DbType.StringFixedLength, DbType.StringFixedLength, DbType.StringFixedLength, _ DbType.StringFixedLength, DbType.StringFixedLength, DbType.StringFixedLength, DbType.Int32}
Dim lclRet As Integer = LoadUserCommand.ExecuteNonQuery()
Dim tmpDomainName As String tmpDomainName = LoadUserCommand.Parameters(6).Value.ToString 'read the name back
Dim tmpMsgNumber = LoadUserCommand.Parameters(7).Value 'read the message number back
Select Case tmpMsgNumber 'not written yet as I haven't got this far!!! Case 1 'pacs username already exists Case 2 'pas username already exists Case 3 'execute fine and all names updated
can somebody let me understand how this Output parameters works in sp_executesql / Dynamic TSQL
I want to store a integer value resulting from executing a dynamic query. I was trying to follow the syntax but to be honest I didn't get it, and resulted in err. So can somebody help me in understanding how it works and how and where to declare the variables to be output and what command does that Output etc.
I'm trying to output all the values selected through a multivalue parameter, but I am having difficulties. In the text box, the parameter contains only four member functions (value, label, count, ismultivalue). However, to output a value or label for the parameter you need to specify an index in the form of:
Parameters!Names.Value(0)
Ideally, I would like to be able to obtain all these values with a function call, but it doesn't look like there is one. Any ideas of how to get all the values?
i built a stored procedure with inserting in to customers table. i have one column with identity. so want to take that identity column value in the same stored procedure. so how can i write that procedure with insert in to statements in that stored procedures.
can any one tell me. also how to get that value in ado.net 2.0. friends please tell me.
I am able to perform my Stored Procedure, but I can't figure out how to get access to the OUTPUT parameters!Here is my code: Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim mySqlDataSource As SqlDataSource = New SqlDataSource
' Add Some Parameter Dim someParameter As Parameter = New Parameter("someParameterName", TypeCode.String) someParameter.DefaultValue = New String("Some Value") someParameter.Direction = Data.ParameterDirection.Output
I've an existing SQL 2000 Stored Procedure that return data in many (~20) output parameters.
I'm starting to use it in a .Net c# application and it seems to insist that I setup all the output parameters: SqlParameter param = cmd.Parameters.Add("@BackgroundColour",SqlDbType.TinyInt); param.Direction=ParameterDirection.Output; even if I only need the value of a single one. Is this right? Is there a way to avoid coding every one every time?
EXEC('SELECT COUNT(docid) AS Total FROM docs WHERE ' + @QueryFilter)I want to get the cound as an output parameter.I can get output parameters to work only when I dont use EXEC. I need to use EXEC for this case since @QueryFilter gets generated in the stored procedure based on some some other data.How can I get that count using ouput parameter?
I am using SqlDataSource programmatically in my data access layer - mainly for convenience but it does generally work fine with no obvious performance issues. The problem I have is with getting back an output parameter. I have an insert-type stored procedure (in Sql Server 2005) operating on a table with an identity column as the primary key: ALTER PROCEDURE [dbo].[InsertAlbum](@ArtistID int, @Title nvarchar(70), @NewID int OUTPUT)ASDECLARE @err intINSERT INTO dbo.ALBUMS (ARTISTID, TITLE)VALUES (@ArtistID, @Title)SELECT @err = @@error IF @err <> 0 RETURN @errSET @NewID = SCOPE_IDENTITY() This works fine when run from Sql Server Management Studio and @NewID has the correct value. My data access code is roughly as follows: dsrc = New SqlDataSource()dsrc.ConnectionString = ConnectionStringdsrc.InsertCommand = "InsertAlbum"dsrc.InsertCommandType = SqlDataSourceCommandType.StoredProcedureDim parms As ParameterCollection = dsrc.InsertParametersDim newid As IntegerAddParameter(parms, "ArtistID", TypeCode.Int32, ParameterDirection.Input, 0, album.ArtistID)AddParameter(parms, "Title", TypeCode.String, ParameterDirection.Input, 0, album.Title)Dim p As New Parameter("NewID", TypeCode.Int32)p.Direction = ParameterDirection.Outputparms.Add(p)Try Dim rv As Integer = dsrc.Insert() newid = parms("NewID") Return newidCatch ex As Exception Return -1End Try The row is inserted into the database, but however I try to define and add the NewID parameter it never has a value. Has anyone tried to do this and can tell me what I am doing wrong? Jon
I have the following stored procedure working with an Access 2000 front end. The output parameters returned to Access are both Null when the record is successfully updated (ie when @@Rowcount = 1), but the correct parameters are returned when the update fails. I'm a bit new to using output parameters, but I have them working perfectly with an insert sproc, and they look basically the same. What bonehead error have I made here? The fact that the record is updated indicates to me that the Commit Trans line is being executed, so why aren't the 2 output parameters set?
TIA
EDIT: Solved, sort of. I found that dropping the "@ResNum +" from "@ResNum + ' Updated'" resolved the problem (@ResNum is an input parameter). This implies that the variable lost its value between the SQL statement and the If/Then, since the SQL correctly updates only the appropriate record from the WHERE clause. Is this supposed to happen? I looked in BOL, and if it's addressed there I missed it.
CREATE PROCEDURE [procResUpdate]
Various input parameters here,
@RetCode as int Output, @RetResNum as nvarchar(15) Output
AS
Declare @RowCounter int
Begin Tran
UPDATE tblReservations SET Various set statements here, LastModified = @LastModified + 1 WHERE ResNum = @ResNum AND LastModified = @LastModified
SELECT @RowCounter = @@ROWCOUNT
If @RowCounter = 1 Begin Commit Tran Select @RetCode = 1 Select @RetResNum = @ResNum + ' Updated' End Else Begin Rollback Tran Select @RetCode = 0 Select @RetResNum = 'Update Failed' End GO
I've written a stored procedure that returns a value via an output parameter.
I'm calling the stored procedure in an sql session is a loop, and it passes the value back correctly the first time, but all subsequent calls the output parameter appears to have the same value. I believe that I'm making some very basic mistake, but I can't work it out.
Here's how I'm calling the stored procedure several times begin declare @instrumentid int exec GetInstrumentId 'OFX,AUDCHF,p,1,2007-03-20 16:54:21.843,2007-06-20,100.1', @instrumentid output; select @instrumentid exec GetInstrumentId 'OFX,AUDUSD,c,2,2007-03-20 16:54:21.843,2007-06-20,100.2', @instrumentid output; select @instrumentid exec GetInstrumentId 'OFX,AUDCHF,p,3,2007-03-20 16:54:21.843,2007-06-20,100.3', @instrumentid output; select @instrumentid end
And this is the start of the stored procedure
Create PROCEDURE [dbo].[GetInstrumentId] (@Ticket varchar(250), @InstrumentId int output) AS
If I call the stored procedure once it gives the corect output, if I call it several times the output parameter (@instrumentid ) never changes.
The following is a code snippit. My main interests are the OUT and OUTPUT parameter keywords. One returns a single value, and the other seemingly a resultset. OUTPUT returns a single value, however OUT seems to return a list of values. Could I please get this confirmed?
Also, I cannot see how the value being returned by OUT is being iterated...
Any help on the obove two matters is appreciated.
Thank You
Chris
BEGIN SNIPPET----------------------------------------------------------------------------------------------------------
--The following example creates the Production.usp_GetList
--stored procedure, which returns a list of products that have
--prices that do not exceed a specified amount.
USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL
We seem to have an problem with using sp_executesql and retrieving back NVARCHAR output parameters. We are trying to dynamically set string values using string templates ( for English and other languages that require using NVARCHAR parameters ). A test case is:
I am trying to create a proc and at the end of the proc I want to call another proc and pass to one of the parameters to proc using the result from the "OUTPUT". Is it possible to use the results from the "OUTPUT" and use them as parameters?
USE [MyDB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
Hello, how can i merge together several select statements? I have something like this: CREATE PROCEDURE Forum_GetThreads @ID int,@AscDesc bitASBEGINSET NOCOUNT ON;SELECT * FROM forum_ansageSELECT * FROM forum_topics WHERE (status = 0) ORDER BY (created) DESCIF (@AscDesc = 0)BEGIN SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) DESCENDELSEBEGIN SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) ASCENDHere i want to merge them all together and return only one SELECT statement with all the data
WHERE (MPI.MPI_NBR=MPI_PERSON.MPI_NBR) AND (MPI.ADDR_NBR=MPI_CURRENT_ADDR.ADDR_NBR) AND MPI.CREATE_DT>=20070101 ORDER BY MPI.CREATE_DT
SELECT PATIENT.PAT_NBR,PATIENT.PHYS_NBR, PHYSICIAN.FIRST_NM,PHYSICIAN.LAST_NM,PHYSICIAN.DE_NBR, PHYSICIAN.SALUTATION_CD FROM PATIENT, PHYSICIAN WHERE PATIENT.PHYS_NBR=PHYSICIAN.PHYS_NBR
