Help with sp_executesql and return parameter

I am trying to use dynamic sql with a return parameter, but with limited success. I am using WebMatrix, vb.net and MSDE to perform this routine. Can someone please clue me in. I have read two fine articles by <a href='http://www.algonet.se/~sommar/dyn-search.html>Erland Sommarskog</a> on dynamic sql using sp_executesql, as well as the somewhat opaque article by Microsoft (262499) on the subject.

While there may be other ways to accomplish this task, I am interested in making it work with dynamic SQL. In production, there will be over 20 parameters coming from the vb.net to the SQL, being driven from user input. Then those same variables will be used to actually retrieve the records to a datagrid.

So with a tip of the cap to Rod Serling, I submit this small code and SQL for your consideration from my Twilight Zone:

 Public Function totalrecordsbysql(list as arraylist) as integer
 dim RetVal as new integer
 dim querystring as string
 Dim cn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("Indiafriend"))
 Dim cmd As SqlCommand = New SqlCommand("SimpleDynProfileCount", cn)
 cmd.commandtype = commandtype.storedprocedure
 dim mydr as SqlDataReader
 cmd.Parameters.add("@.TotalRecords",SqlDbType.int).direction=ParameterDirection.Output
 cmd.Parameters.add("@.age",sqldbtype.int).value = 18
 cn.Open()
 try
 mydr=cmd.executereader()
 catch e as sqlexception
 dim err as sqlerror
 dim strErrorString as string
 for each err in e.Errors
 strErrorString += "SqlError: #" & err.Number.ToString () & vbCRLF + err.Message
 trace.write("sqlexception",strErrorString)
 Next
 finally
 RetVal = cmd.parameters("@.TotalRecords").value
 end try
 Return RetVal
 cn.close()
 End Function

Now here is the stored procedure:

CREATE PROCEDURE SimpleDynProfileCount
@.age int,
@.TotalRecords int output
AS
Declare @.sql nvarchar(4000),
@.paramlist nvarchar(4000)
select @.sql = 'select @.xTotalRecords = count(*) from profile where 1 = 1 '
// RAISERROR(@.sql, 16, 1)
IF @.age > 0
Select @.sql = @.sql + ' AND age > @.xage '
Select @.paramlist = '@.xage int, @.xTotalRecords int output'
Execute sp_executesql @.sql,@.paramlist,@.age,@.xTotalRecords = @.TotalRecords output
select @.TotalRecords
GO

Please note the commented RAISERROR statement. If I uncomment this statement, I will get a return value of 11 records. If I leave it out, I get zero records.
The data is the database should return 11 records, based on the criteria of age > 11Your code works fine for me in Query Analyzer -- with one exception. I needed to change the double forward slashes (//) to double dashes (--) in front of the RAISERROR.

You might also place a SET NOCOUNT ON at the top of your stored procedure. This will suppress the information "xx items selected" messages and will avoid having them returned inadvertently as a resultset.

Terri|||That's what is so strange: I don't receive error messages, just the wrong answer (zero). The double dashes are just artistic license: I didn't remember the comment tag. I just eliminate the line, altogether, in production.|||Try adding SET NOCOUNT ON after your AS at the top of the stored procedure.

Terri|||I have added the statement, but the results are the same. Works fine with the RAISERROR statement, but returns zero without the statement.

I don't want to just leave the RAISERROR statement active. It will come back to haunt me later.

rod|||I have resolved the syntax.

I made a mistake in the vb.net when I used a datareader to retrieve the output value!

Instead of:

mydr=cmd.executereader()

the syntax should be:

cmd.executeNonQuery()

Using this syntax I removed from the Stored Procedure, the RAISERROR statement and the last SELECT @.totalrecords.

After doing all that, everything worked as expected.

-rod

help with sp_executesql

hi i'm trying to do this

DECLARE @.VARR Nvarchar(2500)
DECLARE @.VARR1 Nvarchar(2500)
DECLARE @.VARC Nvarchar(2000)
DECLARE @.VARP Nvarchar(1000)

SET @.VARC= @.VARC + ' DECLARE @.nBKN1 NUMERIC(3,0) DECLARE @.nAPP1 NUMERIC(2,0) DECLARE @.nACT1 char(20)'
SET @.VARC= @.VARC + ' DECLARE @.nGRP1 NUMERIC(2,0) DECLARE @.nCSH1 NUMERIC(12,2) DECLARE @.nNCH1 NUMERIC(12,2) DECLARE @.nTOT1 NUMERIC(12,2) DECLARE @.nCHB1 NUMERIC(12,2) '

@.VARR1 AND @.VARR AND @.VARP is similar to @.VARC. At the end i trying to open a Cursor.. all the query are inside in @.varc + @.varr + @.var1 + @.varp

WHEN I EXECUTE THIS

EXEC('EXEC sp_executesql ' + @.VARC + @.VARR + @.VARR1 + @.VARP )

THE RESULT IS

Procedure 'sp_executesql' expects parameter '@.statement', which was not supplied.

WHY?

DECLARE @.VARR Nvarchar(2500)
DECLARE @.VARR1 Nvarchar(2500)
DECLARE @.VARC Nvarchar(4000) -- nvarchar(max) SQL 2005

DECLARE @.VARP Nvarchar(1000)

SET @.VARC= @.VARC + ' DECLARE @.nBKN1 NUMERIC(3,0) DECLARE @.nAPP1 NUMERIC(2,0) DECLARE @.nACT1 char(20)'
SET

@.VARC= @.VARC + ' DECLARE @.nGRP1 NUMERIC(2,0) DECLARE @.nCSH1

NUMERIC(12,2) DECLARE @.nNCH1 NUMERIC(12,2) DECLARE @.nTOT1 NUMERIC(12,2)

DECLARE @.nCHB1 NUMERIC(12,2) '

@.VARR1

AND @.VARR AND @.VARP is similar to @.VARC. At the end i trying to open a

Cursor.. all the query are inside in @.varc + @.varr + @.var1 + @.varp

WHEN I EXECUTE THIS

set @.Varc = 'EXEC sp_executesql' + @.VARC + @.VARR + @.VARR1 + @.VARP

EXEC(@.Varc)|||

the real problem is that @.varc and @.VARR1 AND @.VARR AND @.VARP has more that 4000 chars... and i know that sp_executesql Store only accept ntext, nvarchar, image.... i using sql server 2000

i'm trying to do this

create PROCEDURE store_xxx1
(
@.i_AMLCBANKNU INT,
@.i_PERIOD INT,
@.dPRC DATETIME,
@.sDay INT,
@.eDay INT,
@.i_AMLDPERIFR DATETIME,
@.i_AMLDPERITO DATETIME
)
AS
-- SET OPTION DATFMT = *MDY, DATSEP = *DASH
BEGIN
DECLARE @.sSTM varchar(8000)--8132
DECLARE @.sSTMAUX varchar(5000)--8132
DECLARE @.VARR Nvarchar(2500)
DECLARE @.VARR1 Nvarchar(2500)
DECLARE @.VARC Nvarchar(2000)
DECLARE @.VARP Nvarchar(1000)

DECLARE @.sQTY VARCHAR(1024)
DECLARE @.sCSH VARCHAR(1024)
DECLARE @.sNCH VARCHAR(1024)
DECLARE @.sTOT VARCHAR(1024)
DECLARE @.sCHB VARCHAR(1024)
DECLARE @.sTGP VARCHAR(1024)
DECLARE @.nBKN NUMERIC(3,0) --DEFAULT 1
DECLARE @.nAPP NUMERIC(2,0)-- DEFAULT 0
DECLARE @.nACT NUMERIC(1,0)-- DEFAULT 0
DECLARE @.nACC NUMERIC(12,0) --DEFAULT 0
DECLARE @.nQTY NUMERIC(9,0) --DEFAULT 0
DECLARE @.nGRP NUMERIC(2,0) --DEFAULT 0
DECLARE @.nCSH NUMERIC(12,2) --DEFAULT 0
DECLARE @.nNCH NUMERIC(12,2) --DEFAULT 0
DECLARE @.nTOT NUMERIC(12,2) --DEFAULT 0
DECLARE @.nCHB NUMERIC(12,2) --DEFAULT 0
DECLARE @.nTGP NUMERIC(12,2) --DEFAULT 0
DECLARE @.sSEL VARCHAR(96) --DEFAULT 'SELECT AMLCBANKNU,AMLCAPPCOD,AMLCACCTYP,AMLCACCOUN,AMLCGRUTRA, '
DECLARE @.iDay INT
DECLARE @.xDay INT
DECLARE @.v_contador INT
DECLARE @.at_end int --default 0

-- DECLARE Not_Found CONDITION FOR SQLSTATE '02000'
-- DECLARE C1 DYNAMIC SCROLL CURSOR FOR sSQLSTM
DECLARE @.C1 CURSOR
--SETEANDO LA VARIABLES DEFAULT
SET @.nBKN =@.i_AMLCBANKNU
SET @.nAPP =0
SET @.nACT =0
SET @.nACC =0
SET @.nQTY =0
SET @.nGRP =0
SET @.nCSH =0
SET @.nNCH =0
SET @.nTOT =0
SET @.nCHB =0
SET @.nTGP =0
SET @.sQTY = ''
SET @.sSTM = ''
SET @.sQTY = ''
SET @.sCSH = ''
SET @.sNCH = ''
SET @.sTOT = ''
SET @.sCHB = ''
SET @.sTGP = ''
SET @.VARR = ''
SET @.sSEL= 'SELECT AMLCBANKNU,AMLCAPPCOD,AMLCACCTYP,AMLCACCOUN,AMLCGRUTRA, '
SET @.at_end=0

SET @.VARR1=''
SET @.VARP=''
--DECLARE CONTINUE HANDLER FOR Not_Found
SET @.at_end = 1
SET @.iDay = @.sDay

IF (@.sDay > @.eDay)
BEGIN
SET @.xDay = @.eDay
SET @.eDay = 31 + @.eDay
END

WHILE (@.iDay < @.eDay)
BEGIN
SET @.sQTY = @.sQTY + 'AMLNQTYT' + CONVERT(VARCHAR(2),@.iDay) + '+'
SET @.sCSH = @.sCSH + 'AMLNCSHT' + CONVERT(VARCHAR(2),@.iDay)+ '+'
SET @.sNCH = @.sNCH + 'AMLNNCHT' + CONVERT(VARCHAR(2),@.iDay)+ '+'
SET @.sTOT = @.sTOT + 'AMLNTOTT' + CONVERT(VARCHAR(2),@.iDay)+ '+'
SET @.sCHB = @.sCHB + 'AMLNCHBT' + CONVERT(VARCHAR(2),@.iDay)+ '+'
SET @.sTGP = @.sTGP + 'AMLTOGRP' + CONVERT(VARCHAR(2),@.iDay)+ '+'
SET @.iDay = @.iDay + 1
IF (@.iDay =32)
BEGIN
SET @.iDay=01
SET @.eDay=@.xDay
END
END

SET @.sQTY = ((@.sQTY + ('AMLNQTYT' + CONVERT(VARCHAR(2),@.iDay))) + ', ')
SET @.sCSH = ((@.sCSH + ('AMLNCSHT' + CONVERT(VARCHAR(2),@.iDay))) + ', ')
SET @.sNCH = ((@.sNCH + ('AMLNNCHT' + CONVERT(VARCHAR(2),@.iDay))) + ', ')
SET @.sTOT = ((@.sTOT + ('AMLNTOTT' + CONVERT(VARCHAR(2),@.iDay))) + ', ')
SET @.sCHB = ((@.sCHB + ('AMLNCHBT' + CONVERT(VARCHAR(2),@.iDay))) + ', ')
SET @.sTGP = ((@.sTGP + ('AMLTOGRP' + CONVERT(VARCHAR(2),@.iDay))) + ' ')
SET @.VARR = @.sSEL + @.sQTY + @.sCSH + @.sNCH + @.sTOT + @.sCHB + @.sTGP
SET @.VARR1 = ' FROM PAMLMSTCNS WHERE AMLCBANKNU='
+ CONVERT(VARCHAR(5),@.i_AMLCBANKNU) + ' AND (' + SUBSTRING (@.sQTY, 1, LEN(@.sQTY)-1)
+ ' > 0 OR ' + SUBSTRING (@.sCSH, 1, LEN(@.sCSH)-1)
+ ' > 0 OR ' + SUBSTRING (@.sNCH, 1, LEN(@.sNCH)-1)
+ ' > 0 OR ' + SUBSTRING (@.sTOT, 1, LEN(@.sTOT)-1)
+ ' > 0 OR ' + SUBSTRING (@.sCHB, 1, LEN(@.sCHB)-1)
+ ' > 0 OR ' + @.sTGP + ' > 0)'

--PREPARE sSQLSTM FROM @.sSTM
-- DECLARE C1 CURSOR FOR @.sSTM

IF (@.i_PERIOD <> 5)
BEGIN
SELECT @.v_contador= COUNT(*)
FROM PAMLDETCNS
WHERE AMLCBANKNU = @.nBKN
AND AMLCCONSOL = @.i_PERIOD
END

IF (@.v_contador<>0)
BEGIN
DELETE FROM PAMLDETCNS
WHERE AMLCBANKNU = @.nBKN
AND AMLCCONSOL = @.i_PERIOD
END

SET @.VARC=''
SET @.VARC= @.VARC + ' DECLARE @.nBKN1 NUMERIC(3,0) DECLARE @.nAPP1 NUMERIC(2,0) DECLARE @.nACT1 NUMERIC(1,0) DECLARE @.nACC1 NUMERIC(12,0) DECLARE @.nQTY1 NUMERIC(9,0) '
SET @.VARC= @.VARC + ' DECLARE @.nGRP1 NUMERIC(2,0) DECLARE @.nCSH1 NUMERIC(12,2) DECLARE @.nNCH1 NUMERIC(12,2) DECLARE @.nTOT1 NUMERIC(12,2) DECLARE @.nCHB1 NUMERIC(12,2) '
SET @.VARC= @.VARC + ' DECLARE @.nTGP1 NUMERIC(12,2) DECLARE @.i_PERIOD1 INT '
SET @.VARC= @.VARC + ' SET @.nBKN1 =' + CONVERT(CHAR(15),@.i_AMLCBANKNU )
SET @.VARC= @.VARC + ' SET @.i_PERIOD1 =' + CONVERT(CHAR(15),@.i_PERIOD )
SET @.VARC= @.VARC + ' SET @.nAPP1 =0'
SET @.VARC= @.VARC + ' SET @.nACT1 =0'
SET @.VARC= @.VARC + ' SET @.nACC1 =0'
SET @.VARC= @.VARC + ' SET @.nQTY1 =0'
SET @.VARC= @.VARC + ' SET @.nGRP1 =0'
SET @.VARC= @.VARC + ' SET @.nCSH1 =0'
SET @.VARC= @.VARC + ' SET @.nNCH1 =0'
SET @.VARC= @.VARC + ' SET @.nTOT1 =0'
SET @.VARC= @.VARC + ' SET @.nCHB1 =0'
SET @.VARC= @.VARC + ' SET @.nTGP1 =0'
SET @.VARC = @.VARC + ' DECLARE C10 CURSOR FOR '

SET @.VARP = @.VARP + ' OPEN C10 FETCH NEXT FROM C10 '
SET @.VARP = @.VARP + ' INTO @.nBKN1,@.nAPP1,@.nACT1,@.nACC1,@.nGRP1, @.nQTY1,@.nCSH1,@.nNCH1,@.nTOT1,@.nCHB1,@.nTGP1'
SET @.VARP = @.VARP + ' WHILE @.@.FETCH_STATUS = 0'
SET @.VARP = @.VARP + ' BEGIN'
SET @.VARP = @.VARP + ' INSERT INTO PAMLDETCNS'
SET @.VARP = @.VARP + ' (AMLCBANKNU,AMLCAPPCOD,AMLCACCTYP,AMLCACCOUN,AMLCGRUTRA,AMLCCONSOL,AMLDPERIFR,AMLDPERITO,AMLNQTYTRC,AMLNCHSTRC,AMLNNCHTRC,AMLNTOTTRC,AMLNCHBTRC, AMLTOTGRPC)'
SET @.VARP = @.VARP + ' VALUES '
SET @.VARP = @.VARP + ' (@.nBKN1,@.nAPP1,@.nACT1,@.nACC1,@.nGRP1,@.i_PERIOD1,' +CHAR(39)+CONVERT(CHAR(10),@.i_AMLDPERIFR,101)+CHAR(39)+','+CHAR(39)+CONVERT(CHAR(10),@.i_AMLDPERITO,101)+CHAR(39)+',@.nQTY1,@.nCSH1,@.nNCH1,@.nTOT1,@.nCHB1,@.nTGP1)'
SET @.VARP = @.VARP + ' FETCH NEXT FROM C10 '
SET @.VARP = @.VARP + ' INTO @.nBKN1,@.nAPP1,@.nACT1,@.nACC1,@.nGRP1, @.nQTY1,@.nCSH1,@.nNCH1,@.nTOT1,@.nCHB1,@.nTGP1'
SET @.VARP = @.VARP + ' END '
SET @.VARP = @.VARP + ' CLOSE C10 '
SET @.VARP = @.VARP + ' DEALLOCATE C10 '

print LEN(@.sSTM)
print @.VARC + @.VARR
print @.VARR1 + @.VARP
--EXEC sp_executesql @.VARC

EXEC('EXEC sp_executesql ' + @.VARC + @.VARR + @.VARR1 + @.VARP )

END
as you can see my store open a cursor inside of the query.... any suggestion?

thks

Help with sp_executesql

I have a full sql statement which was generated dynamicly, and need to
execute that string and then take the output and generate a
spreadsheet document based on the output. I'm new to sql and the book
I have doesn't really explain much. Anyone with an example of their
work would be appreaciated.
thank you.use output option
for example:

select @.sql = 'select @.bdate=min(effective_date) from ' + @.table
SET @.ParmDefinition = N'@.bdate datetime OUTPUT'
EXEC sp_executesql @.sql, @.ParmDefinition, @.bdate OUTPUT

"Ado" <a3vr6tur@.hotmail.com> wrote in message
news:848bd3a0.0401151001.7270c50b@.posting.google.c om...
> I have a full sql statement which was generated dynamicly, and need to
> execute that string and then take the output and generate a
> spreadsheet document based on the output. I'm new to sql and the book
> I have doesn't really explain much. Anyone with an example of their
> work would be appreaciated.
> thank you.|||"Ado" <a3vr6tur@.hotmail.com> wrote in message
news:848bd3a0.0401151001.7270c50b@.posting.google.c om...
> I have a full sql statement which was generated dynamicly, and need to
> execute that string and then take the output and generate a
> spreadsheet document based on the output. I'm new to sql and the book
> I have doesn't really explain much. Anyone with an example of their
> work would be appreaciated.
> thank you.

The Books Online syntax documentation for sp_executesql has two examples,
and the subject "Using sp_executesql" has several more. You'll probably also
find this useful:

http://www.sommarskog.se/dynamic_sql.html

If you're still having problems after checking those sources, perhaps you
could post a (simple) example of what you're trying to do.

Simonsql