Passing Paramenter to SP for Column Name

[Michael Jackson]

I need to select status values form 1 of 4 possible columns, and I need to
pass the column name to select on as a parameter to the stored procedure.
Does anyone have an example of the syntax for the stored procedure?

such as:

CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
(
@SelectColumn nVarChar
)
AS

SET NOCOUNT ON;

SELECT *
FROM Documents
WHERE (@SelectColumn = 0)

The columns to select on are BIT columns.

The error message on the above SP is:

'Syntax error converting the nvarchar value 'P' to a column of data type
int.'

At this point, the passed in parameter is a string "ProducerStatus"

Thanks
Michael

[SSK]

hi Michael,
You need to use the Dynamic Sql to change the column name at
the run time.

create procedure dbo.sp_Document_select_bystatus
(@selectColumn varchar(255))
as
set nocount on
declare @dynamicSql varchar(8000)

select @dynamicSql = '
SELECT *
FROM Documents
WHERE ( ' + @selectColumn + ' = 0)
'
execute (@dynamicSql)
set nocount off
Go

Thank you
santhosh
Michael Jackson wrote:

I need to select status values form 1 of 4 possible columns, and I need to pass the column name to select on as a parameter to the stored procedure. Does anyone have an example of the syntax for the stored procedure?

such as:

CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
(
@SelectColumn nVarChar
)
AS

SET NOCOUNT ON;

SELECT *
FROM Documents
WHERE (@SelectColumn = 0)

The columns to select on are BIT columns.

The error message on the above SP is:

'Syntax error converting the nvarchar value 'P' to a column of data type int.'

At this point, the passed in parameter is a string "ProducerStatus"

Thanks
Michael

[Michael Jackson]

Thanks for the help. It worked great.
"SSK" <su*******@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

hi Michael,
You need to use the Dynamic Sql to change the column name at
the run time.

create procedure dbo.sp_Document_select_bystatus
(@selectColumn varchar(255))
as
set nocount on
declare @dynamicSql varchar(8000)

select @dynamicSql = '
SELECT *
FROM Documents
WHERE ( ' + @selectColumn + ' = 0)
'
execute (@dynamicSql)
set nocount off
Go

Thank you
santhosh
Michael Jackson wrote:

I need to select status values form 1 of 4 possible columns, and I

need to

pass the column name to select on as a parameter to the stored

procedure.

Does anyone have an example of the syntax for the stored procedure?

such as:

CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
(
@SelectColumn nVarChar
)
AS

SET NOCOUNT ON;

SELECT *
FROM Documents
WHERE (@SelectColumn = 0)

The columns to select on are BIT columns.

The error message on the above SP is:

'Syntax error converting the nvarchar value 'P' to a column of data

type

int.'

At this point, the passed in parameter is a string "ProducerStatus"

Thanks
Michael

[David Portas]

Avoid dynamic SQL if you can. In this case you don't need it:

SELECT col1
FROM Documents
WHERE col1 = 0 AND @selectcolumn = 'col1'
UNION ALL
SELECT col2
FROM Documents
WHERE col2 = 0 AND @selectcolumn = 'col2'
UNION ALL
SELECT col3
FROM Documents
WHERE col3 = 0 AND @selectcolumn = 'col3'
UNION ALL
SELECT col4
FROM Documents
WHERE col4 = 0 AND @selectcolumn = 'col4'

To understand why dynamic SQL isn't a good idea for this, see:

http://www.sommarskog.se/dynamic_sql.html

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Michael Jackson (st********@cox.net) writes:

I need to select status values form 1 of 4 possible columns, and I need to
pass the column name to select on as a parameter to the stored procedure.
Does anyone have an example of the syntax for the stored procedure?

such as:

CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
(
@SelectColumn nVarChar
)
AS

To add to the other responses, permit me to point out two other flaws:

1) sp_ is a prefix that is reserved for system procedures, and SQL Server
will first look for these in master. Don't use it for your own code.

2) nvarchar without lengthspeciication is the same as nvarchar(1), hardly
what you want.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp