Passing column names to a stored procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Passing column names to a stored procedure?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-22 : 07:32:29

Marv writes "I'm passing in the parameter @Field which contains a column name in the Globals table, the value of which I want returned in the @Value output parameter. The procedure always returns the value of the @Field parameter in the @Value parameter rather than the columns database value. What am I doing wrong?

Thanks in advance for any assistance.

Marv

===============================================================



ALTER PROCEDURE dbo.GetGlobals

	(
		@Field nvarchar(250) = NULL,
		@Value nvarchar(1000) OUTPUT
	)
AS
	SELECT  @Value = @Field
	FROM         Globals
	WHERE     (GlobalID = 1) 
	SET NOCOUNT ON
	RETURN

"

dsdeming

479 Posts

Posted - 2003-04-22 : 08:23:25

Your statement

SELECT @Value = @Field
FROM Globals
WHERE (GlobalID = 1)
SET NOCOUNT ON

merely assigns the value in the @Field parameter to the @Value parameter and outputs it. The value you pass in to @Field is not recognized as a column name in this context. One of the most common uses of dynamic SQL is when table or column names are used as parameters. See EXECUTE and sp_executesql in BOL.

In addition, note that SET NOCOUNT ON must preceed the select statement if you want to suppress its rowcount.

HTH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources