Dynamic Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic Query Help

Author

Topic

SpeshulK926
Starting Member

16 Posts

Posted - 2007-09-28 : 10:15:45

It looks like I have everything correct on my dynamic query, but I can't quite figure out how to run it. I've never run a query with an output parameter before... Can someone take a look at this and tell me what's missing?

ALTER   PROCEDURE [dbo].[ap_GetLetterIDNumForResume] --'cor1','1088637639'

@TableName char(10),
@AccountNumber char(10),
@SequenceNumber int = NULL OUTPUT

AS

DECLARE @sql varchar(1048)
SET @sql = 'select @sequencenumber = max(sequence_ID) from ' + @tablename + ' where accountnumber = ' + @accountnumber

Execute sp_executesql @sql

I get an error when i try to run it:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 14
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-28 : 10:19:53

[code]DECLARE @sql nvarchar(1048)[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 10:19:59

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

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-28 : 10:22:15

[code]ALTER PROCEDURE [dbo].[ap_GetLetterIDNumForResume] --'cor1','1088637639'

@TableName CHAR(10),
@AccountNumber CHAR(10),
@SequenceNumber int = NULL OUTPUT

AS

DECLARE @sql nvarchar(1048)
SET @sql = 'SELECT @sequencenumber = MAX(sequence_ID) FROM ' + @tablename + ''' WHERE accountnumber = ''' + @accountnumber

Execute sp_executesql @sql,
N'@SequenceNumber int OUTPUT',
@SequenceNumber OUTPUT
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SpeshulK926
Starting Member

16 Posts

Posted - 2007-09-28 : 10:23:34

that worked great, but got a new error. This is where the "I don't know how to use an output parameter" comes into play... I've tried searching around, but there's just so much of it and I couldn't find a specific example of what I'm missing. I want it to pull back the SequenceNumber it finds, but it keeps telling me

Must declare the variable @sequencenumber

How do I declare it if I don't know what it is yet... Thanks...

Edit:

You beat me to it Khtan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-28 : 10:26:12

read the link that Peso provided

KH
[spoiler]Time is always against us[/spoiler]

SpeshulK926
Starting Member

16 Posts

Posted - 2007-09-28 : 10:27:54

quote:
Originally posted by khtan

read the link that Peso provided

KH
[spoiler]Time is always against us[/spoiler]

Everything works great now. I know there are downsides to doing queries like this, but unfortunately, my office requires us to use stored procedures for everything and I have to access over 200 tables all with the exact same fields, but different table names... Nothing I can really do about it :( I'm gonna read up some more on it though to make sure I'm doing other stuff correctly. Thanks for all your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources