Help with first dynamic SQL attempt
-
I am trying to write a stored procedure that is basically used to return the value of a specified field from a table. Here's my Stored Procedure:
CREATE PROCEDURE Z_Billing_Payment
@GetWhat as Varchar(15),
@PaymentID as int,
@fResult as varchar(50) = "" OUTPUT
AS
Declare @SQL VarChar(1000)
SELECT @SQL = 'SELECT @fResult = ' + @GetWhat + ' From Payments '
SELECT @SQL = @SQL + ' Where PaymentID = ' + str( @PaymentID)
Exec ( @SQL)
GO
_______________ Here's what I receive when I try to execute
Running dbo."Z_Billing_Payment" ( @GetWhat = AccountNumber, @PaymentID = 3, @fResult = <DEFAULT> ).
Must declare the variable '@fResult'.
No rows affected.
(0 row(s) returned)
@fResult =
@RETURN_VALUE = 0
Finished running dbo."Z_Billing_Payment".
_____________________________
Any comments on why I get the undeclared variable message and wheter this is a sound approach to retrieving a given field from a specified record...
Thanks
-
Always a good thing to read http://www.sommarskog.se/dynamic_sql.html when it comes to this topic.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
CREATE PROCEDURE Z_Billing_Payment
(@GetWhat as Varchar(15),
@PaymentID as int)
--set @GetWhat = 'something'
--set @PaymentID = 1
as
Declare @SQL VarChar(1000)
SET @SQL = 'SELECT '+@GetWhat+' From Payments Where PaymentID = '
+cast(@PaymentID as varchar (10))
--PRINT @SQL
Exec ( @SQL)
alays useful when building dynamic SQL to use print statement in QA as you can test what the SQL is by pasting the results back into QA to see if it runs as expected
good luck
There are 10 types of people in this world - those that understand binary and those that don't
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply