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_ByStatu s
(
@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]

Re: Passing Paramenter to SP for Column Name

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

create procedure dbo.sp_Document _select_bystatu s
(@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:[color=blue]
> I need to select status values form 1 of 4 possible columns, and I[/color]
need to[color=blue]
> pass the column name to select on as a parameter to the stored[/color]
procedure.[color=blue]
> Does anyone have an example of the syntax for the stored procedure?
>
> such as:
>
> CREATE PROCEDURE dbo.sp_Document _Select_ByStatu s
> (
> @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[/color]
type[color=blue]
> int.'
>
> At this point, the passed in parameter is a string "ProducerStatus "
>
> Thanks
> Michael[/color]

[Michael Jackson]

Re: Passing Paramenter to SP for Column Name

Thanks for the help. It worked great.


"SSK" <suthramsk@yaho o.com> wrote in message
news:1107491299 .712183.231340@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> hi Michael,
> You need to use the Dynamic Sql to change the column name at
> the run time.
>
> create procedure dbo.sp_Document _select_bystatu s
> (@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:[color=green]
>> I need to select status values form 1 of 4 possible columns, and I[/color]
> need to[color=green]
>> pass the column name to select on as a parameter to the stored[/color]
> procedure.[color=green]
>> Does anyone have an example of the syntax for the stored procedure?
>>
>> such as:
>>
>> CREATE PROCEDURE dbo.sp_Document _Select_ByStatu s
>> (
>> @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[/color]
> type[color=green]
>> int.'
>>
>> At this point, the passed in parameter is a string "ProducerStatus "
>>
>> Thanks
>> Michael[/color]
>[/color]

[David Portas]

Re: Passing Paramenter to SP for Column Name

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]

Re: Passing Paramenter to SP for Column Name

Michael Jackson (stratojack@cox .net) writes:[color=blue]
> 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_ByStatu s
> (
> @SelectColumn nVarChar
> )
> AS[/color]

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 lengthspeciicat ion is the same as nvarchar(1), hardly
what you want.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp