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.
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 OUTPUTASDECLARE @sql varchar(1048)SET @sql = 'select @sequencenumber = max(sequence_ID) from ' + @tablename + ' where accountnumber = ' + @accountnumberExecute sp_executesql @sql I get an error when i try to run it: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 14Procedure 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 OUTPUTASDECLARE @sql nvarchar(1048)SET @sql = 'SELECT @sequencenumber = MAX(sequence_ID) FROM ' + @tablename + ''' WHERE accountnumber = ''' + @accountnumberExecute 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 @sequencenumberHow 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! |
|
|
|
|
|
|
|