I'm still a newbie and can't get this dynamic SQL statement to work. Any
help appreciated.
Declare @.sqlStmt nvarchar(500)
Declare @.PatNo int
Declare @.Range int
set @.PatNO = 240
Set @.Range = 36000
--EXEC --('insert Into ' + @.TempVS +
SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
Result, Units, abn, Range
FROM PatLabVal2 where PatNo = @.PatNo '
EXEC @.sqlstmt
I get an error :
Server: Msg 2812, Level 16, State 62, Line 11
Could not find stored procedure 'Select Distinct patNo, DateDrawn,
Sort,TestnameDisplay,
Result, Units, abn, Range
FROM PatLabVal2 where PatNo = @.PatNo '.
Thanks for the help,
Stephen K. MiyasatoDon't call Exec. Use sp_execsql instead. Look it up in books online for
examples, but you're about 90% of the way to implementing it.
Regards
Colin Dawson
www.cjdawson.com
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:O5jeNtScGHA.3856@.TK2MSFTNGP03.phx.gbl...
> I'm still a newbie and can't get this dynamic SQL statement to work. Any
> help appreciated.
> Declare @.sqlStmt nvarchar(500)
> Declare @.PatNo int
> Declare @.Range int
> set @.PatNO = 240
> Set @.Range = 36000
> --EXEC --('insert Into ' + @.TempVS +
> SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '
> EXEC @.sqlstmt
> I get an error :
> Server: Msg 2812, Level 16, State 62, Line 11
> Could not find stored procedure 'Select Distinct patNo, DateDrawn,
> Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '.
> Thanks for the help,
> Stephen K. Miyasato
>|||Thanks for the help.
Stephen K. Miyasato
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:oH47g.65228$wl.53809@.text.news.blueyonder.co.uk...
> Don't call Exec. Use sp_execsql instead. Look it up in books online for
> examples, but you're about 90% of the way to implementing it.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
> news:O5jeNtScGHA.3856@.TK2MSFTNGP03.phx.gbl...
>|||Just adding to Colin's comment.
don't use nvarchar unless u are sure about unicode character. use varchar.
and
for executing dynamic sql
use
EXEC(@.sqlstmt)
again. This is just for the syntax.
Use sp_executesql. Its better in code reuse and does the same job from a
developers standpoint.
--
"Stephen K. Miyasato" wrote:
> I'm still a newbie and can't get this dynamic SQL statement to work. Any
> help appreciated.
> Declare @.sqlStmt nvarchar(500)
> Declare @.PatNo int
> Declare @.Range int
> set @.PatNO = 240
> Set @.Range = 36000
> --EXEC --('insert Into ' + @.TempVS +
> SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '
> EXEC @.sqlstmt
> I get an error :
> Server: Msg 2812, Level 16, State 62, Line 11
> Could not find stored procedure 'Select Distinct patNo, DateDrawn,
> Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '.
> Thanks for the help,
> Stephen K. Miyasato
>
>|||Stephen K. Miyasato (miyasat@.flex.com) writes:
> I'm still a newbie and can't get this dynamic SQL statement to work. Any
> help appreciated.
> Declare @.sqlStmt nvarchar(500)
> Declare @.PatNo int
> Declare @.Range int
> set @.PatNO = 240
> Set @.Range = 36000
> --EXEC --('insert Into ' + @.TempVS +
> SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '
> EXEC @.sqlstmt
> I get an error :
> Server: Msg 2812, Level 16, State 62, Line 11
> Could not find stored procedure 'Select Distinct patNo, DateDrawn,
> Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '.
You need parentheses after EXEC to execute dynamic SQL. Now you are
trying to execute a stored procedure of which the name is in @.sqlstmt.
Again, I recommend you to read my article
http://www.sommarskog.se/dynamic_sql.html. That will give you the
answer to this question, it will also sort out some problems that
you have run into yet. And you may even find out that you don't need
dynamic SQL at all.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
No comments:
Post a Comment