Passing Paramenter to SP for Column Name

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
Michaelhi 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|||Thanks for the help. It worked great.

"SSK" <suthramsk@.yahoo.com> wrote in message
news:1107491299.712183.231340@.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|||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
--|||Michael Jackson (stratojack@.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, esquel@.sommarskog.se

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

Passing data to SQL Server

How to pass big set of data from ASP to SQL Server stored procedure (for example 30 rows and 5 columns - content of html table) ?Stored procedures cannot accept recordsets as parameters, so you will need to pass the data to the procedure one record at a time.

Another alternative is to insert your data into a staging table. Then your application either calls the stored procedure or you create a scheduled job that calls the stored procedure. The procedure checks the staging table for records and processes them as a set.

blindman|||What about XML?sql

Passing columns to CLR function

Hello,

I am trying to send to colums to SQL CLR function and get some results. I want the CLR code be like:

Code Snippet

public void DoSomething(SqlDouble[] a, SqlDouble[] b, out SqlDouble x, out SqlDouble y, out SqlDouble z)

{

//Do Something...

x = ....

y=...

z=...

}

I want to call this code from SQL code:

Code Snippet

create table #Temp (float a,float b)

declare @.x float

declare @.y float

declare @.z float

exec dbo.DoSomething(a,b,@.x,@.y,@.z) ?

Do someone have an idea?

Hi Shlomi,

There are many tutorials for achieving this on the net and even in BOL. Look for "CLR User-Defined Functions" Books Online for examples.

In short: If you're using Visual Studio, you open the right project and choose Add Function. Sceleton code is presented and you add your code. Now build the Assembly. After building, go to SQL Server and user CREATE ASSEMBY to import the assembly into SQL Server and last use CREATE FUNCTION to import the function from the assembly. Now you can use your function in SQL Server like you want to.

Passing Column Name as Parameter (SPROC)

Hello, I don't know a lot of T-SQL. I have a table with 49 columns and 48 rows (US States). The fist column is a list of "from" states and the other columns are named for each state. The point is to look up a variable column for a variable row to obtain a state to state transit rate. I tried everything I could find and no luck. If someone can help I would appreciate it, and if not thanks anyway. Gregg

You could use dynamic SQL from this problem:

Code Snippet

create table rates

(

from_state char(2),

WA decimal,

NY decimal,

CA decimal

)

insert into rates values('WA',0,1,10)

insert into rates values('NY',2,0,20)

insert into rates values('CA',3,30,0)

create procedure GetRate

@.from_state char(2),

@.to_state char(2)

AS

BEGIN

declare @.query varchar(100)

set @.query = 'select '+@.to_state+' from rates where from_state='''+@.from_state+''''

execute ( @.query)

END

But this method has SQL Injections.

Partly you could solve this problem by following code:

Code Snippet

create procedure GetRate2

@.from_state char(2),

@.to_state char(2)

AS

BEGIN

declare @.query nvarchar(100)

set @.query = 'select '+@.to_state+' from rates where from_state=@.from_state'

EXEC sp_executesql @.query, N'@.from_state char(2)', @.from_state=@.from_state

END

But best solution is convert you table for following format:

Code Snippet

create table rates2

(

from_state char(2),

to_state char(2),

rate decimal

)

|||

Here the sample ..

Code Snippet

--Sample table (first 10 States)

Create Table #statesfare (

[Starting From] Varchar(100) ,

[Alabama] Varchar(100) ,

[Alaska] Varchar(100) ,

[Arizona] Varchar(100) ,

[Arkansas] Varchar(100) ,

[California] Varchar(100) ,

[Colorado] Varchar(100) ,

[Connecticut] Varchar(100) ,

[Delaware] Varchar(100) ,

[Florida] Varchar(100) ,

[Georgia] Varchar(100)

);

--Random Generated data (Not For Scale, But City From & To same then ZERO)

Insert Into #statesfare Values('Alabama','0','21','29','90','82','65','72','84','4','51');

Insert Into #statesfare Values('Alaska','33','0','17','80','37','92','90','21','12','20');

Insert Into #statesfare Values('Arizona','62','19','0','97','23','66','22','43','94','60');

Insert Into #statesfare Values('Arkansas','61','38','12','0','98','42','68','70','81','87');

Insert Into #statesfare Values('California','92','27','82','72','0','43','84','39','24','80');

Insert Into #statesfare Values('Colorado','72','34','97','52','52','0','10','38','64','40');

Insert Into #statesfare Values('Connecticut','100','78','27','18','74','3','0','67','26','48');

Insert Into #statesfare Values('Delaware','93','49','20','88','45','3','60','0','57','77');

Insert Into #statesfare Values('Florida','79','80','37','12','90','30','24','48','0','50');

Insert Into #statesfare Values('Georgia','33','46','16','30','46','72','42','85','18','0');

Declare @.StartFrom as varchar(100);

Declare @.EndAt as varchar(100);

--Sample Input

Set @.StartFrom = 'Alabama'

Set @.EndAt = 'Georgia'

--Using Dynamic SQL (Supports Both SQL Server 2000 & 2005)

Exec ('Select ' + @.EndAt + ' Fare From #statesfare Where [Starting From] =''' +@.StartFrom + '''');

--Using UNPIVOT operator Only on SQL Server 2005

Select

[Starting From]

,[Target States]

,Fares

From

#statesfare P

UNPIVOT

(

Fares FOR [Target States] IN

(

[Alabama],

[Alaska],

[Arizona],

[Arkansas],

[California],

[Colorado],

[Connecticut],

[Delaware],

[Florida],

[Georgia]

)

) as UPVT

Where

[Starting From] = @.StartFrom

And [Target States] = @.EndAt

|||

I recommend to use quotename() function to avoid sql injection.

Code Snippet

set @.qeury = 'select ' + quotename(@.to_sate) + ' from ....';

quotename('abc') returns '[abc]';

Regards,

|||This is my table

FROM AL AR AZ CA AL 1.75 1.95 1.10 1.75 AR 1.50 1.50 1.50 1.75 AZ 1.50 1.50 1.50 1.75 CA 1.50 1.50 1.50 1.75

I Need to use a stored procedure to select one of these decimal rates based on two input parameters (@.fromstate AND @.tostate) and return the rate as an out put parameter(@.rate). I tried this even though I knew it was too simple to worrk.

ALTER PROCEDURE RC_Get_Rate

(

@.origstate nvarchar(255),

@.deststate nvarchar(255),

@.rate nvarchar(255) OUTPUT

)

AS

SET NOCOUNT ON

BEGIN

SELECT @.rate = @.deststate

FROM Rates48

WHERE Origin_State = @.origstate

END

RETURN

I'm sorry if you're previous posts already answered this, I thought I might not have been clear enougn as to my situation and my goal. I really did search a lot on this topic but got nowhere. Again if you can help (or already did) thanks, and if you can't thanks anyway, Gregg|||

here you go...

Code Snippet

Create Table rates48 (

[Origin_State] Varchar(100) ,

[AL] float ,

[AR] float ,

[AZ] float ,

[CA] float

);

Go

Insert Into rates48 Values('AL','1.75','1.95','1.10','1.75');

Insert Into rates48 Values('AR','1.50','1.50','1.50','1.75');

Insert Into rates48 Values('AZ','1.50','1.50','1.50','1.75');

Insert Into rates48 Values('CA','1.50','1.50','1.50','1.75');

Go

Create PROCEDURE RC_Get_Rate

(

@.origstate nvarchar(255),

@.deststate nvarchar(255),

@.rate nvarchar(255) OUTPUT

)

AS

SET NOCOUNT ON

BEGIN

Declare @.Query as NVarchar(1000);

Set @.Query = N'SELECT @.rateout = ' + quotename(@.deststate) + '

FROM Rates48

WHERE Origin_State = @.origstatein';

Exec sp_executesql @.Query, N'@.origstatein varchar(255), @.rateout nvarchar(255) output',@.origstatein = @.origstate, @.rateout=@.rate OUTPUT;

Return;

END

Go

Declare @.rate nvarchar(255)

Exec RC_Get_Rate 'AL', 'AZ', @.rate OUTPUT

Select @.rate

|||Thank You very much Manivannan.D.Sekaran. That works very well. I must admit though I don't know how. Could you maybe recommend a good book I could pick up to further myself on T-SQL. I would like to learn SPROC's, Functions, and some more advanced SQL. Well, again, thank you very much Manivannan.D.Sekaran, and also thank you to everyone who took the time to read my post and help me out. I really appreciate it. Thanks, Gregg

Passing a column into a stored proc?

I'm writing a simple voting script and have columns for each options. I need to update the data based on whichever option the user picks.

I.e...

If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1
If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1
Etc., etc.

What's the best way to do that without building an ad-hoc SQL statement? There could be many options so I dont want to have lots of redundant SQL statements.

Can I just use a varible in a stored proc and do something like this?

UPDATE mytable SET @.optionUserpicked=@.optionUserpicked + 1

Thanks in advance

You can't really.

The best way is to redesign your table, so that it looks like this:

VoteID / Option (or optionID) / Votes

1,1,0

1,2,0

Then you can execute something like this:

UPDATE MyTable SET votes=votes+1 WHERE VoteID=1 ANDOption=@.option

Assuming that you are going to have multiple "polls", each uses a different VoteID. Each poll can then also have a variable number of options. It will also make reporting the final results easier as well.

|||

Maybe we can make a trick using dynamic SQL. For exampe:

create table myTable (UID int identity(1,1),option1 int,option2 int,option3 int)
go
INSERT INTO myTable (option1,option2,option3) SELECT 0,0,0
go
CREATE PROCEDURE sp_UpdVote @.opName sysname='option1',@.pkCol sysname='UID'
AS
IF (@.opName=@.pkCol)
RAISERROR('Can''t update the primary key',16,1)
ELSE
IF (exists(SELECT name FROM syscolumns
WHERE id=OBJECT_ID('myTable') ANDname=@.opName))
EXEC('UPDATE myTable SET ['+@.opName+']= ['+@.opName+']+1')
ELSE RAISERROR('There is no column named [%s] in this table.',16,1,@.opName)
go

EXEC sp_UpdVote

go
SELECT * FROM myTable

Pass data in script component when no transformation needed

Hi,
I have 56 fields coming into the input of an script component, The need for script component was to just to check if one of those 56 columns has a valid date or not, If valid it will parse and put in an output date column, if not, it will put in NULL.

The 55 fields should be passed on. I dont really wanna write code and define output columns. How do I do this ?

Any input in this would be appreciated.

Thanks,As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.|||

jwelch wrote:

As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.

To expand a little, as long as you set the script component to be a "transformation," this is true.

You'll be able to get at them by using Row.XXXX

Just make sure that they are set to READWRITE in the properties when you select the columns to be used in the script component. That way you can assign a value.

The PROBLEM is that you can't change the metadata, so if all of your 55+ columns are strings, but can contain dates, you won't be able to output a datetime data type using the same column name. You'll have to create a NEW output column name, which will have to be done by creating new outputs. You can, however, convert a datetime field to a string inside the script component and will then be able to reuse the column.

Does this make sense?|||

Phil Brammer wrote:

Does this make sense?

Makes me confuse, because I am able to dominate columns by using Script component. I just check its checkbox and it appears in Input list, then I add output column with same name and it works.

Are you talking about something else ?|||

It sounds like you are using an asynchronous output, as a synchronous output throws an error if you create an output column with the same name as an input column.

You might try using a synchronous output. Go to the Inputs and Outputs page of the property dialog, and add an output. Select the new output, and in the properties set the SynchronousInputID to the input for the component. All of the input columns will be available on the synchronous output.

Pass data in script component when no transformation needed

Hi,
I have 56 fields coming into the input of an script component, The need for script component was to just to check if one of those 56 columns has a valid date or not, If valid it will parse and put in an output date column, if not, it will put in NULL.

The 55 fields should be passed on. I dont really wanna write code and define output columns. How do I do this ?

Any input in this would be appreciated.

Thanks,As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.|||

jwelch wrote:

As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.

To expand a little, as long as you set the script component to be a "transformation," this is true.

You'll be able to get at them by using Row.XXXX

Just make sure that they are set to READWRITE in the properties when you select the columns to be used in the script component. That way you can assign a value.

The PROBLEM is that you can't change the metadata, so if all of your 55+ columns are strings, but can contain dates, you won't be able to output a datetime data type using the same column name. You'll have to create a NEW output column name, which will have to be done by creating new outputs. You can, however, convert a datetime field to a string inside the script component and will then be able to reuse the column.

Does this make sense?|||

Phil Brammer wrote:

Does this make sense?

Makes me confuse, because I am able to dominate columns by using Script component. I just check its checkbox and it appears in Input list, then I add output column with same name and it works.

Are you talking about something else ?|||

It sounds like you are using an asynchronous output, as a synchronous output throws an error if you create an output column with the same name as an input column.

You might try using a synchronous output. Go to the Inputs and Outputs page of the property dialog, and add an output. Select the new output, and in the properties set the SynchronousInputID to the input for the component. All of the input columns will be available on the synchronous output.