Help with first dynamic SQL attempt

Question

Help with first dynamic SQL attempt

Jeff Stinson

SSC Rookie

Points: 42
More actions
December 1, 2004 at 7:40 am

#87223

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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]

Dave Bell-174911 SSC Journeyman Points: 84 More actions · Answer 2

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