passing in a value to use as a column 'as name' in a stored proc

Hi,
I want to hand into a store procude the column name to use in the returned
result set...
create proc sample
@.colName as nvarcher(20)
as
select col1 as @.colname, col2 from table1..
But this produces an error... saying incorrect syntax near @.colname
is there a way to do want i am trying to do here?
ThanksThe curse and blessings of dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Martin C K Poon
Senior Analyst Programmer
====================================
"Aussie Rules" <AussieRules@.nospam.nospam> bl
news:uvQ1pgOjGHA.3572@.TK2MSFTNGP04.phx.gbl g...
> Hi,
> I want to hand into a store procude the column name to use in the returned
> result set...
> create proc sample
> @.colName as nvarcher(20)
> as
> select col1 as @.colname, col2 from table1..
> But this produces an error... saying incorrect syntax near @.colname
> is there a way to do want i am trying to do here?
> Thanks
>
>
>|||Thanks for Martin's informative inputs.
Hi Aussie,
I agree with Martin that you would need to consider using the dynamic SQL
execution. And in SQL Server the "exec" or "execute" keyword to execute
dynamic generated T-SQL statements:
#EXECUTE
http://msdn.microsoft.com/library/e...asp?frame=true
BTW, dynamic sql will have additional performance overhead comparing to
static T-SQL execution. Also, when we use string concatenate to generate
dynamic dynamic T-SQL statement, we would also take care of SQL injection
issue:
#SQL Injection
http://msdn2.microsoft.com/en-us/library/ms161953.aspx
Hope this also helps.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Aussie,
Have you got any progress or new ideas on this issue or does our replies
help you some? If there is still anything we can help, please feel free to
post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

passing data to SQL database

hello everybody,

i hope u can help me with this... i want to pass the data entered in a web page to a stored procedure so as to store the data in a database... am using three layered architecture... how can i do this...

thanks in advance..........

Look at my reponse in thread http://forums.asp.net/thread/1636021.aspx. There are stored procedures there to create insert, update, listall, select and delete together with the required wrapper code in either C# or vb.net. The required C# library is there. I will post an updated Vb.NET version after my MSDN subscription gets delivered.

Passing Column Name as parameter to sql store procedure

i am using asp.net 2005 with sql server 2005. in my database table contains

Table Name : Page_Content

Page_Id

1011021AbcPqr2Lmnoiu

ALTER PROCEDURE[dbo].[SELECT_CONTENT]

(@.lang_codevarchar(max))

AS

begin

declare@.aas varchar(max)set@.a = @.lang_code

Selectpage_id,@.aFrompage_content

end

Here in this above store procedure i want to pass 101 to @.lang_code

here is my output, but this is wrong output

Page_Id

Column111012101

but i want following output

Page_Id

1011021AbcPqr2Lmnoiu

use dynamic sql.http://www.sommarskog.se/dyn-search.html
modify your procedure as:-

declare @.sql
set @.sql = 'Selectpage_id,' + @.a + 'Frompage_content'
exec sp_executesql @.sql

hope it helps

|||

aadreja:

use dynamic sql.http://www.sommarskog.se/dyn-search.html
modify your procedure as:-

declare @.sql
set @.sql = 'Selectpage_id,' + @.a + 'Frompage_content'
exec sp_executesql @.sql

hope it helps

The above code is subject to sql injection attacks. Query on sql injection attacks if you don't know what they are.

As coded, someone could force your page to reveal sensitive data in other tables, or alter or destroy data in your database in ways you do not want to allow.

Given that a column name has very specific naming rules, you can test that the value you get in @.a is a plausible, safe column name.

If @.a has any character other than a letter from a-z, A-Z or 0-9, you should trap that and raise an error.

One way to test is to make a copy of @.a and remove all the valid characters. If nothing is left in the copy, it's a safe column name to process.

|||

I know you could do this with dynamic sql, but that's not always the best solution.

I think you could use a CASE/WHEN block to do what you are wanting. Each field would have to be known in advance, this wouldn't work "on the fly" if you add new columns to the table without updating the SP

ALTER PROCEDURE[dbo].[SELECT_CONTENT]

(@.lang_codevarchar(max))

AS

begin

declare@.aas varchar(max)set@.a = @.lang_code

Selectpage_id,
CASE
WHEN @.a = '101' THEN 101
WHEN @.a = '102' THEN 102
ELSE 101 -- you don't need an else, but this query will fail in a syntax error if the input doesn't match one of your defined values.
END Frompage_content

end

|||

I agree!

Benefits of your approach:

Sql Injection safe

Passing a variable to a job

Is it possible to pass a global variable to a SQL Server 2000 job? Or
is there another solution that I can use - I don't want to store what
I'm passing through as plain text.
Thanks,
SaulHi
Do you mean if you call a stored procedure you'd want to pass a paremeter to
this sp from the client?
As far as I know you cannot do that , however you may want to consider
using DTS Packages which do accepts parameters
<saulmarg@.gmail.com> wrote in message
news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...
> Is it possible to pass a global variable to a SQL Server 2000 job? Or
> is there another solution that I can use - I don't want to store what
> I'm passing through as plain text.
> Thanks,
> Saul
>|||Hi Uri,
I'm trying to get a VBA app to call a job to call a DTS package which
calls the BCP application to transfer data between two databases. The
BCP app needs a password to access the server.
Any suggestions?
Uri Dimant wrote:
> Hi
> Do you mean if you call a stored procedure you'd want to pass a paremeter to
> this sp from the client?
> As far as I know you cannot do that , however you may want to consider
> using DTS Packages which do accepts parameters
>
>
> <saulmarg@.gmail.com> wrote in message
> news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...
> > Is it possible to pass a global variable to a SQL Server 2000 job? Or
> > is there another solution that I can use - I don't want to store what
> > I'm passing through as plain text.
> >
> > Thanks,
> > Saul
> >|||Why do you call a job that activates the DTS package? You can activate
the DTS package directly from the application. If you'll activate the
DTS, you'll be able to pass it parameters. If for some reason you have
to use job between the application and the DTS package, then you can
insert you parameter into a table before you run the job. In the
begining of the job you run a select statement on that table in get the
value that was inserted into the table.
Adi
saulmarg@.gmail.com wrote:
> Hi Uri,
> I'm trying to get a VBA app to call a job to call a DTS package which
> calls the BCP application to transfer data between two databases. The
> BCP app needs a password to access the server.
> Any suggestions?
>
> Uri Dimant wrote:
> > Hi
> >
> > Do you mean if you call a stored procedure you'd want to pass a paremeter to
> > this sp from the client?
> >
> > As far as I know you cannot do that , however you may want to consider
> > using DTS Packages which do accepts parameters
> >
> >
> >
> >
> > <saulmarg@.gmail.com> wrote in message
> > news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...
> > > Is it possible to pass a global variable to a SQL Server 2000 job? Or
> > > is there another solution that I can use - I don't want to store what
> > > I'm passing through as plain text.
> > >
> > > Thanks,
> > > Saul
> > >|||Thanks Adi. I use the job because the VBA app doesn't have features to
record history of jobs etc. The only problem with putting the
parameter into a table is that it's a password which I'd prefer not to
store anywhere. Is there any built in SQL functionality to encrypt and
decrypt?
Adi wrote:
> Why do you call a job that activates the DTS package? You can activate
> the DTS package directly from the application. If you'll activate the
> DTS, you'll be able to pass it parameters. If for some reason you have
> to use job between the application and the DTS package, then you can
> insert you parameter into a table before you run the job. In the
> begining of the job you run a select statement on that table in get the
> value that was inserted into the table.
> Adi
> saulmarg@.gmail.com wrote:
> > Hi Uri,
> >
> > I'm trying to get a VBA app to call a job to call a DTS package which
> > calls the BCP application to transfer data between two databases. The
> > BCP app needs a password to access the server.
> >
> > Any suggestions?
> >
> >
> > Uri Dimant wrote:
> > > Hi
> > >
> > > Do you mean if you call a stored procedure you'd want to pass a paremeter to
> > > this sp from the client?
> > >
> > > As far as I know you cannot do that , however you may want to consider
> > > using DTS Packages which do accepts parameters
> > >
> > >
> > >
> > >
> > > <saulmarg@.gmail.com> wrote in message
> > > news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...
> > > > Is it possible to pass a global variable to a SQL Server 2000 job? Or
> > > > is there another solution that I can use - I don't want to store what
> > > > I'm passing through as plain text.
> > > >
> > > > Thanks,
> > > > Saul
> > > >|||Unfortunately there isn't a built in functionality to encrypt and
decrypt in SQL Server 2000. You can try and develop an extended stored
procedure that does it, but this seems to be a to big and to
complicated task to do:-). I'm sure that there are other companies
that developed such extended stored procedure, but since I didn't need
one I can't recommend a good product (and of course this will cost you
some money). Maybe you can still use the table. You can play around
with the table's permissions and not let anyone run select on this
table. You can let the user that you use in the connection string run
only insert on the table (and not even select) and in the job run the
select statement that will get the password (I assume that the SQL
agent is configured to run under the administrator's account, so the
job would be able to run the select statement).
Adi
saulmarg@.gmail.com wrote:
> Thanks Adi. I use the job because the VBA app doesn't have features to
> record history of jobs etc. The only problem with putting the
> parameter into a table is that it's a password which I'd prefer not to
> store anywhere. Is there any built in SQL functionality to encrypt and
> decrypt?
>
> Adi wrote:
> > Why do you call a job that activates the DTS package? You can activate
> > the DTS package directly from the application. If you'll activate the
> > DTS, you'll be able to pass it parameters. If for some reason you have
> > to use job between the application and the DTS package, then you can
> > insert you parameter into a table before you run the job. In the
> > begining of the job you run a select statement on that table in get the
> > value that was inserted into the table.
> >
> > Adi
> >
> > saulmarg@.gmail.com wrote:
> > > Hi Uri,
> > >
> > > I'm trying to get a VBA app to call a job to call a DTS package which
> > > calls the BCP application to transfer data between two databases. The
> > > BCP app needs a password to access the server.
> > >
> > > Any suggestions?
> > >
> > >
> > > Uri Dimant wrote:
> > > > Hi
> > > >
> > > > Do you mean if you call a stored procedure you'd want to pass a paremeter to
> > > > this sp from the client?
> > > >
> > > > As far as I know you cannot do that , however you may want to consider
> > > > using DTS Packages which do accepts parameters
> > > >
> > > >
> > > >
> > > >
> > > > <saulmarg@.gmail.com> wrote in message
> > > > news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...
> > > > > Is it possible to pass a global variable to a SQL Server 2000 job? Or
> > > > > is there another solution that I can use - I don't want to store what
> > > > > I'm passing through as plain text.
> > > > >
> > > > > Thanks,
> > > > > Saul
> > > > >

Passing a variable to a job

Is it possible to pass a global variable to a SQL Server 2000 job? Or
is there another solution that I can use - I don't want to store what
I'm passing through as plain text.
Thanks,
SaulHi
Do you mean if you call a stored procedure you'd want to pass a paremeter to
this sp from the client?
As far as I know you cannot do that , however you may want to consider
using DTS Packages which do accepts parameters
<saulmarg@.gmail.com> wrote in message
news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...
> Is it possible to pass a global variable to a SQL Server 2000 job? Or
> is there another solution that I can use - I don't want to store what
> I'm passing through as plain text.
> Thanks,
> Saul
>|||Hi Uri,
I'm trying to get a VBA app to call a job to call a DTS package which
calls the BCP application to transfer data between two databases. The
BCP app needs a password to access the server.
Any suggestions?
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> Do you mean if you call a stored procedure you'd want to pass a paremeter
to
> this sp from the client?
> As far as I know you cannot do that , however you may want to consider
> using DTS Packages which do accepts parameters
>
>
> <saulmarg@.gmail.com> wrote in message
> news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...|||Why do you call a job that activates the DTS package? You can activate
the DTS package directly from the application. If you'll activate the
DTS, you'll be able to pass it parameters. If for some reason you have
to use job between the application and the DTS package, then you can
insert you parameter into a table before you run the job. In the
begining of the job you run a select statement on that table in get the
value that was inserted into the table.
Adi
saulmarg@.gmail.com wrote:[vbcol=seagreen]
> Hi Uri,
> I'm trying to get a VBA app to call a job to call a DTS package which
> calls the BCP application to transfer data between two databases. The
> BCP app needs a password to access the server.
> Any suggestions?
>
> Uri Dimant wrote:|||Thanks Adi. I use the job because the VBA app doesn't have features to
record history of jobs etc. The only problem with putting the
parameter into a table is that it's a password which I'd prefer not to
store anywhere. Is there any built in SQL functionality to encrypt and
decrypt?
Adi wrote:[vbcol=seagreen]
> Why do you call a job that activates the DTS package? You can activate
> the DTS package directly from the application. If you'll activate the
> DTS, you'll be able to pass it parameters. If for some reason you have
> to use job between the application and the DTS package, then you can
> insert you parameter into a table before you run the job. In the
> begining of the job you run a select statement on that table in get the
> value that was inserted into the table.
> Adi
> saulmarg@.gmail.com wrote:|||Unfortunately there isn't a built in functionality to encrypt and
decrypt in SQL Server 2000. You can try and develop an extended stored
procedure that does it, but this seems to be a to big and to
complicated task to do:-). I'm sure that there are other companies
that developed such extended stored procedure, but since I didn't need
one I can't recommend a good product (and of course this will cost you
some money). Maybe you can still use the table. You can play around
with the table's permissions and not let anyone run select on this
table. You can let the user that you use in the connection string run
only insert on the table (and not even select) and in the job run the
select statement that will get the password (I assume that the SQL
agent is configured to run under the administrator's account, so the
job would be able to run the select statement).
Adi
saulmarg@.gmail.com wrote:[vbcol=seagreen]
> Thanks Adi. I use the job because the VBA app doesn't have features to
> record history of jobs etc. The only problem with putting the
> parameter into a table is that it's a password which I'd prefer not to
> store anywhere. Is there any built in SQL functionality to encrypt and
> decrypt?
>
> Adi wrote:

Passing a list/array to an SQL Server stored procedure 2005

Hi, I m using sql 2005 as a back end in my application...

I am useing Store procedure..for my data in grid..

ALTERPROCEDURE [dbo].[ProductZoneSearct]

(

@.Productidchar(8),
@.Pronamechar(8),
@.radiusint,
@.modevarchar(5)='M',
@.Zonenvarchar(1000),

)

AS
SETNOCOUNTON;
Create Table #Product (ProductID int, TimeEntered datetime, DateAvailable datetime, Productname varchar(80), City varchar(50), State char(4),Miles decimal, Payment varchar(40),UserID int, Phone varchar(15))

Insert #Product
Select ProductID , TimeEntered, DateAvailable, Productname ,City,State,miles,Payment
,Miles, UserID, Daily, Phone
From [tblproduct]
Where city IN (@.Zone)

Select ProductID TimeEntered, DateAvailable, Productname City,State,miles,Payment
,Miles, U.Phone As phoneNumber, Company, , L.Phone As cmpPhone
From #Product As L
Left Join (Select UserID, Company, Phone, From [User]) As U On U.UserID = L.UserID
Order By DateAvailable

if i pass value in"where city in (@.Zone)" and@.Zone ='CA','AD','MH' then it can not get any result..but if writewhere city in ('CA','AD','MH') then it give me perfact result..

I tried to below syntax also but in no any user
Where city IN ('+@.Zone+')

In short if i pass value through varibale (@.Zone) then i cant get result...but if i put direct value in query then only getting result..can anybody tell me what is problem ?

Please Hel[p me !!!

Thank you !!!

Check out this blog post:

Passing lists to SQL Server 2005 with XML Parameters

|||

Hmmm... you may be better off writing an object data source and filtering the data after the select. Otherwise, have a look at sp_executesql or somesuch...

|||

Problem is If i pass only one value into variable then it gives me result but if i pass more than one value then it wount give me result.
Example..If i pass @.Zone='KS' then it works fine but if i pass @.Zone='KS','MS' Then it wong give me data...coz "," (comma) .seperated..may be it count after comma seprateion its a new value...or i dont know why i m not getting result

Please help me

Thank you & Regards.

|||

Sorry I am not getting you..:-((

|||

Instead of passing the parameters to the stored procedure, you can just select everything, and filter the data after. Look at FilterParameters and FilterExpression for SQL DataSource. I think you can set the FilterExpression="WHERE Region IN (@.Region)", and add @.Region as a FilterParameter, or something like that. Otherwise, you can make an ObjectDataSource that executes the stored procedure, and you can filter the resultset inside there however you want.

EDIT

Well, the whole Filter parameter thing with an "IN" clause isn't working for me... however, I think you can handle the datasource's filtering event to build the filter expression.

pass a value

is it possible to do:
(A)
declare @.numberofitems Int
@.numberofitems = select max(itemorder)
from store, department, etc.

and pass the @.numberofitems to a #tempStore table, like:
(B)
(store, department, @.numberofitems,...)

I got itemorder but not the number of items in each department
Alex

--
Sent by 3 from yahoo part from com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/newHi

You don;t give enough detail to produce an exact query. Please post DDL,
example data (as insert statements) and expected output if you require a
more precise answer.

At a guess something like:

SELECT S.Store, D.Department, max(I.itemorder) as NumberOfItems
from store S JOIN department D ON S.StoreId = D.StoreId
JOIN ItemsOrders I On I.DeptId = D.DeptId
GROUP BY S.Store, D.Department

or

SELECT S.Store, D.Department, ( SELECT max(I.itemorder) FROM ItemsOrders I
WHERE I.DeptId = D.DeptId AND S.StoreId = I.StoreId ) as NumberOfItems
FROM store S JOIN department D ON S.StoreId = D.StoreId

John

etc"alexqa2003@.yahoo.com" <u128845214@.spawnkill.ip-mobilphone.net> wrote in
message news:l.1062471242.1626678466@.host-66-81-78-52.rev.o1.com...
> is it possible to do:
> (A)
> declare @.numberofitems Int
> @.numberofitems = select max(itemorder)
> from store, department, etc.
> and pass the @.numberofitems to a #tempStore table, like:
> (B)
> (store, department, @.numberofitems,...)
> I got itemorder but not the number of items in each department
> Alex
>
>
> --
> Sent by 3 from yahoo part from com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new|||u128845214@.spawnkill.ip-mobilphone.net (alexqa2003@.yahoo.com) wrote in message news:<l.1062471242.1626678466@.host-66-81-78-52.rev.o1.com>...
> is it possible to do:
> (A)
> declare @.numberofitems Int
> @.numberofitems = select max(itemorder)
> from store, department, etc.
> and pass the @.numberofitems to a #tempStore table, like:
> (B)
> (store, department, @.numberofitems,...)
> I got itemorder but not the number of items in each department
> Alex

It's not really clear from your post what you're trying to do, but it
may be something like this:

insert into #tempStore
(store, department, numberofitems)
select store, department, max(itemorder)
from orders
group by store, department

Or maybe this:

insert into #tempStore
(store, department, numberofitems)
select store, department, count(itemorder)
from orders
group by store, department

If this doesn't help, then it would be good if you can post your DDL
(CREATE TABLE statements), along with some sample data and the
expected output.

Simon