SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Dynamic Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SpeshulK926
Starting Member

16 Posts

Posted - 09/28/2007 :  10:15:45  Show Profile  Reply with Quote
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)

Singapore
17635 Posts

Posted - 09/28/2007 :  10:19:53  Show Profile  Reply with Quote
DECLARE @sql nvarchar(1048)



KH
Time is always against us

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 09/28/2007 :  10:19:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
http://www.sommarskog.se/dynamic_sql.html



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 09/28/2007 :  10:22:15  Show Profile  Reply with Quote
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



KH
Time is always against us

Go to Top of Page

SpeshulK926
Starting Member

16 Posts

Posted - 09/28/2007 :  10:23:34  Show Profile  Reply with Quote
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

Edited by - SpeshulK926 on 09/28/2007 10:24:16
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 09/28/2007 :  10:26:12  Show Profile  Reply with Quote
read the link that Peso provided


KH
Time is always against us

Go to Top of Page

SpeshulK926
Starting Member

16 Posts

Posted - 09/28/2007 :  10:27:54  Show Profile  Reply with Quote
quote:
Originally posted by khtan

read the link that Peso provided


KH
Time is always against us





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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000