How can I pass a list of comma seperated values to an IN statememnt?
ie
@.parameter = '1,2,3,4'
select * from table_name where column_name in (@.parameter)
This can be executed dynamically, but is there any other method?In your stored procedure declare a table to hold the data.
Parse the input paramater into the table.
DECLARE @.InList table
( parm int)
)
parse the parameter
Select * from table_name where column_name in (select parm from @.InList)|||No, you cannot do that
1)
Dejan Sarka has posted this script
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@.List As varchar(8000))
RETURNS @.Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @.Item As varchar(8000), @.Pos As int
WHILE DATALENGTH(@.List)>0
BEGIN
SET @.Pos=CHARINDEX(',',@.List)
IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(','),8000)
END
RETURN
END
GO
/* Usage example */
SELECT t1.*
FROM TsqlSplit('10428,10429') AS t1
declare @.inList varchar(50)
set @.inList='10428,10429'
select od.* from [order details] od
INNER JOIN
(SELECT Item
FROM dbo.TsqlSplit(@.InList)) As t
ON od.orderid = t.Item
2)
declare @.sql varchar(50)
set @.sql='1,2,4'
select * from table where charindex(',' + id+ ',',','+ @.sql+',')>0
<newscorrespondent@.charter.net> wrote in message
news:vXEEg.27$Lo3.3@.newsfe07.lga...
> In your stored procedure declare a table to hold the data.
> Parse the input paramater into the table.
> DECLARE @.InList table
> ( parm int)
> )
> parse the parameter
> Select * from table_name where column_name in (select parm from @.InList)|||pravin.nagarajan@.gmail.com wrote:
> How can I pass a list of comma seperated values to an IN statememnt?
> ie
> @.parameter = '1,2,3,4'
> select * from table_name where column_name in (@.parameter)
> This can be executed dynamically, but is there any other method?
>
I have a short post on my web site describing how to do this, but the
site is currently being reconstructed. You can find the original post
in Google's cache by searching for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks I found this solution very helpful.It works
Uri Dimant wrote:
> No, you cannot do that
> 1)
> Dejan Sarka has posted this script
> IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
> DROP FUNCTION dbo.TsqlSplit
> GO
> CREATE FUNCTION dbo.TsqlSplit
> (@.List As varchar(8000))
> RETURNS @.Items table (Item varchar(8000) Not Null)
> AS
> BEGIN
> DECLARE @.Item As varchar(8000), @.Pos As int
> WHILE DATALENGTH(@.List)>0
> BEGIN
> SET @.Pos=CHARINDEX(',',@.List)
> IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
> SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
> IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
> SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(','),8000)
> END
> RETURN
> END
> GO
> /* Usage example */
> SELECT t1.*
> FROM TsqlSplit('10428,10429') AS t1
>
> declare @.inList varchar(50)
> set @.inList='10428,10429'
> select od.* from [order details] od
> INNER JOIN
> (SELECT Item
> FROM dbo.TsqlSplit(@.InList)) As t
> ON od.orderid = t.Item
> 2)
> declare @.sql varchar(50)
> set @.sql='1,2,4'
> select * from table where charindex(',' + id+ ',',','+ @.sql+',')>0
>
> <newscorrespondent@.charter.net> wrote in message
> news:vXEEg.27$Lo3.3@.newsfe07.lga...
> > In your stored procedure declare a table to hold the data.
> > Parse the input paramater into the table.
> >
> > DECLARE @.InList table
> > ( parm int)
> > )
> >
> > parse the parameter
> >
> > Select * from table_name where column_name in (select parm from @.InList)sql
Showing posts with label variables. Show all posts
Showing posts with label variables. Show all posts
Wednesday, March 21, 2012
Multiple variables to an IN sstatement
How can I pass a list of comma seperated values to an IN statememnt?
ie
@.parameter = '1,2,3,4'
select * from table_name where column_name in (@.parameter)
This can be executed dynamically, but is there any other method?In your stored procedure declare a table to hold the data.
Parse the input paramater into the table.
DECLARE @.InList table
( parm int)
)
parse the parameter
Select * from table_name where column_name in (select parm from @.InList)|||No, you cannot do that
1)
Dejan Sarka has posted this script
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@.List As varchar(8000))
RETURNS @.Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @.Item As varchar(8000), @.Pos As int
WHILE DATALENGTH(@.List)>0
BEGIN
SET @.Pos=CHARINDEX(',',@.List)
IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(',
'),8000)
END
RETURN
END
GO
/* Usage example */
SELECT t1.*
FROM TsqlSplit('10428,10429') AS t1
declare @.inList varchar(50)
set @.inList='10428,10429'
select od.* from [order details] od
INNER JOIN
(SELECT Item
FROM dbo.TsqlSplit(@.InList)) As t
ON od.orderid = t.Item
2)
declare @.sql varchar(50)
set @.sql='1,2,4'
select * from table where charindex(',' + id+ ',',','+ @.sql+',')>0
<newscorrespondent@.charter.net> wrote in message
news:vXEEg.27$Lo3.3@.newsfe07.lga...
> In your stored procedure declare a table to hold the data.
> Parse the input paramater into the table.
> DECLARE @.InList table
> ( parm int)
> )
> parse the parameter
> Select * from table_name where column_name in (select parm from @.InList)|||pravin.nagarajan@.gmail.com wrote:
> How can I pass a list of comma seperated values to an IN statememnt?
> ie
> @.parameter = '1,2,3,4'
> select * from table_name where column_name in (@.parameter)
> This can be executed dynamically, but is there any other method?
>
I have a short post on my web site describing how to do this, but the
site is currently being reconstructed. You can find the original post
in Google's cache by searching for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks I found this solution very helpful.It works
Uri Dimant wrote:[vbcol=seagreen]
> No, you cannot do that
> 1)
> Dejan Sarka has posted this script
> IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
> DROP FUNCTION dbo.TsqlSplit
> GO
> CREATE FUNCTION dbo.TsqlSplit
> (@.List As varchar(8000))
> RETURNS @.Items table (Item varchar(8000) Not Null)
> AS
> BEGIN
> DECLARE @.Item As varchar(8000), @.Pos As int
> WHILE DATALENGTH(@.List)>0
> BEGIN
> SET @.Pos=CHARINDEX(',',@.List)
> IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
> SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
> IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
> SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(',
'),8000)
> END
> RETURN
> END
> GO
> /* Usage example */
> SELECT t1.*
> FROM TsqlSplit('10428,10429') AS t1
>
> declare @.inList varchar(50)
> set @.inList='10428,10429'
> select od.* from [order details] od
> INNER JOIN
> (SELECT Item
> FROM dbo.TsqlSplit(@.InList)) As t
> ON od.orderid = t.Item
> 2)
> declare @.sql varchar(50)
> set @.sql='1,2,4'
> select * from table where charindex(',' + id+ ',',','+ @.sql+',')>0
>
> <newscorrespondent@.charter.net> wrote in message
> news:vXEEg.27$Lo3.3@.newsfe07.lga...
ie
@.parameter = '1,2,3,4'
select * from table_name where column_name in (@.parameter)
This can be executed dynamically, but is there any other method?In your stored procedure declare a table to hold the data.
Parse the input paramater into the table.
DECLARE @.InList table
( parm int)
)
parse the parameter
Select * from table_name where column_name in (select parm from @.InList)|||No, you cannot do that
1)
Dejan Sarka has posted this script
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@.List As varchar(8000))
RETURNS @.Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @.Item As varchar(8000), @.Pos As int
WHILE DATALENGTH(@.List)>0
BEGIN
SET @.Pos=CHARINDEX(',',@.List)
IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(',
'),8000)
END
RETURN
END
GO
/* Usage example */
SELECT t1.*
FROM TsqlSplit('10428,10429') AS t1
declare @.inList varchar(50)
set @.inList='10428,10429'
select od.* from [order details] od
INNER JOIN
(SELECT Item
FROM dbo.TsqlSplit(@.InList)) As t
ON od.orderid = t.Item
2)
declare @.sql varchar(50)
set @.sql='1,2,4'
select * from table where charindex(',' + id+ ',',','+ @.sql+',')>0
<newscorrespondent@.charter.net> wrote in message
news:vXEEg.27$Lo3.3@.newsfe07.lga...
> In your stored procedure declare a table to hold the data.
> Parse the input paramater into the table.
> DECLARE @.InList table
> ( parm int)
> )
> parse the parameter
> Select * from table_name where column_name in (select parm from @.InList)|||pravin.nagarajan@.gmail.com wrote:
> How can I pass a list of comma seperated values to an IN statememnt?
> ie
> @.parameter = '1,2,3,4'
> select * from table_name where column_name in (@.parameter)
> This can be executed dynamically, but is there any other method?
>
I have a short post on my web site describing how to do this, but the
site is currently being reconstructed. You can find the original post
in Google's cache by searching for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks I found this solution very helpful.It works
Uri Dimant wrote:[vbcol=seagreen]
> No, you cannot do that
> 1)
> Dejan Sarka has posted this script
> IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
> DROP FUNCTION dbo.TsqlSplit
> GO
> CREATE FUNCTION dbo.TsqlSplit
> (@.List As varchar(8000))
> RETURNS @.Items table (Item varchar(8000) Not Null)
> AS
> BEGIN
> DECLARE @.Item As varchar(8000), @.Pos As int
> WHILE DATALENGTH(@.List)>0
> BEGIN
> SET @.Pos=CHARINDEX(',',@.List)
> IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
> SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
> IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
> SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(',
'),8000)
> END
> RETURN
> END
> GO
> /* Usage example */
> SELECT t1.*
> FROM TsqlSplit('10428,10429') AS t1
>
> declare @.inList varchar(50)
> set @.inList='10428,10429'
> select od.* from [order details] od
> INNER JOIN
> (SELECT Item
> FROM dbo.TsqlSplit(@.InList)) As t
> ON od.orderid = t.Item
> 2)
> declare @.sql varchar(50)
> set @.sql='1,2,4'
> select * from table where charindex(',' + id+ ',',','+ @.sql+',')>0
>
> <newscorrespondent@.charter.net> wrote in message
> news:vXEEg.27$Lo3.3@.newsfe07.lga...
Multiple variables on a Script transformation
I must be doing something wrong here but I can't see what it is. Can anyone enlighten me?
I have a script transformation that I want to pass two variables to through ReadOnlyVariables but for some reason the editor will only accept one. If I try to add a second I get the message "The variable cannot be found".
The help text against ReadOnlyVariables says to enter a comma separated list which I thought would be simply "variable1, variable2" but this doesn't seem to work. I can enter each variable name individually so it is not that I have the names wrong.
Can anyone advise?
Thanks,
AndrewYep, don't put a space between the variables.
i.e. "variable1,variable2"|||Thanks, it hadn't occurred to me that the editor would be quite so fussy. Is this documented anywhere?|||BOL states "Type a comma-separated list of read-only variables that are available to the script." I've submitted feedback saying that perhaps it should mention there mustn't be any spaces in there.
-Jamie|||I'll make the BOL text clearer today. I'm surprised that I didn't hit the same problem when testing. That is rather fussy on the editor's part.
-Doug
Multiple variables in fetch
I can not assign more than one variable using this cursor:
DECLARE Itemid_Cursor CURSOR FOR
select distinct itemid,itemname from items
OPEN Itemid_Cursor
FETCH NEXT FROM Itemid_Cursor
INTO @.itemid,@.itemnameWhat does "can not" mean? Do you get an error message? If so, what is it?
<uri@.bwayphoto.com> wrote in message
news:1139251594.154562.102910@.g43g2000cwa.googlegroups.com...
>I can not assign more than one variable using this cursor:
>
> DECLARE Itemid_Cursor CURSOR FOR
> select distinct itemid,itemname from items
> OPEN Itemid_Cursor
> FETCH NEXT FROM Itemid_Cursor
> INTO @.itemid,@.itemname
>|||The @.itemname variable does not get populated.|||Can you post all of the relevant actual code?
Andrew J. Kelly SQL MVP
<uri@.bwayphoto.com> wrote in message
news:1139251594.154562.102910@.g43g2000cwa.googlegroups.com...
>I can not assign more than one variable using this cursor:
>
> DECLARE Itemid_Cursor CURSOR FOR
> select distinct itemid,itemname from items
> OPEN Itemid_Cursor
> FETCH NEXT FROM Itemid_Cursor
> INTO @.itemid,@.itemname
>|||Well, you're going to have to provide DDL and sample data... what you've
provided so far is not enough to give you an answer.
See http://www.aspfaq.com/5006
<uri@.bwayphoto.com> wrote in message
news:1139251923.570483.247690@.o13g2000cwo.googlegroups.com...
> The @.itemname variable does not get populated.
>|||<uri@.bwayphoto.com> wrote in message
news:1139251923.570483.247690@.o13g2000cwo.googlegroups.com...
> The @.itemname variable does not get populated.
Maybe you have NULLs in that column.|||Thanks Andrew, I removed the non-relevant code and it worked.
I had some other bug.
Thanks again.|||*doh*
<uri@.bwayphoto.com> wrote in message
news:1139253728.769033.207820@.g14g2000cwa.googlegroups.com...
> Thanks Andrew, I removed the non-relevant code and it worked.
> I had some other bug.
> Thanks again.
>|||I tried this and works fine:
-- CREATE TEST TABLE
create table items
(
itemid int
, itemname varchar(50)
)
-- POPULATE TEST TABLE
insert into items
values (1, 'Item1')
insert into items
values (2, 'Item2')
insert into items
values (3, 'Item3')
-- BEGIN SCRIPT
declare @.itemid int
, @.itemname varchar(50)
DECLARE Itemid_Cursor CURSOR FOR
select distinct itemid,itemname from items
OPEN Itemid_Cursor
FETCH NEXT FROM Itemid_Cursor
INTO @.itemid,@.itemname
while @.@.fetch_status = 0
begin
select @.itemid,@.itemname
FETCH NEXT FROM Itemid_Cursor INTO @.itemid,@.itemname
end
close Itemid_Cursor
deallocate Itemid_Cursor
-- DROP TEST TABLE
drop table items
"uri@.bwayphoto.com" wrote:
> I can not assign more than one variable using this cursor:
>
> DECLARE Itemid_Cursor CURSOR FOR
> select distinct itemid,itemname from items
> OPEN Itemid_Cursor
> FETCH NEXT FROM Itemid_Cursor
> INTO @.itemid,@.itemname
>
DECLARE Itemid_Cursor CURSOR FOR
select distinct itemid,itemname from items
OPEN Itemid_Cursor
FETCH NEXT FROM Itemid_Cursor
INTO @.itemid,@.itemnameWhat does "can not" mean? Do you get an error message? If so, what is it?
<uri@.bwayphoto.com> wrote in message
news:1139251594.154562.102910@.g43g2000cwa.googlegroups.com...
>I can not assign more than one variable using this cursor:
>
> DECLARE Itemid_Cursor CURSOR FOR
> select distinct itemid,itemname from items
> OPEN Itemid_Cursor
> FETCH NEXT FROM Itemid_Cursor
> INTO @.itemid,@.itemname
>|||The @.itemname variable does not get populated.|||Can you post all of the relevant actual code?
Andrew J. Kelly SQL MVP
<uri@.bwayphoto.com> wrote in message
news:1139251594.154562.102910@.g43g2000cwa.googlegroups.com...
>I can not assign more than one variable using this cursor:
>
> DECLARE Itemid_Cursor CURSOR FOR
> select distinct itemid,itemname from items
> OPEN Itemid_Cursor
> FETCH NEXT FROM Itemid_Cursor
> INTO @.itemid,@.itemname
>|||Well, you're going to have to provide DDL and sample data... what you've
provided so far is not enough to give you an answer.
See http://www.aspfaq.com/5006
<uri@.bwayphoto.com> wrote in message
news:1139251923.570483.247690@.o13g2000cwo.googlegroups.com...
> The @.itemname variable does not get populated.
>|||<uri@.bwayphoto.com> wrote in message
news:1139251923.570483.247690@.o13g2000cwo.googlegroups.com...
> The @.itemname variable does not get populated.
Maybe you have NULLs in that column.|||Thanks Andrew, I removed the non-relevant code and it worked.
I had some other bug.
Thanks again.|||*doh*
<uri@.bwayphoto.com> wrote in message
news:1139253728.769033.207820@.g14g2000cwa.googlegroups.com...
> Thanks Andrew, I removed the non-relevant code and it worked.
> I had some other bug.
> Thanks again.
>|||I tried this and works fine:
-- CREATE TEST TABLE
create table items
(
itemid int
, itemname varchar(50)
)
-- POPULATE TEST TABLE
insert into items
values (1, 'Item1')
insert into items
values (2, 'Item2')
insert into items
values (3, 'Item3')
-- BEGIN SCRIPT
declare @.itemid int
, @.itemname varchar(50)
DECLARE Itemid_Cursor CURSOR FOR
select distinct itemid,itemname from items
OPEN Itemid_Cursor
FETCH NEXT FROM Itemid_Cursor
INTO @.itemid,@.itemname
while @.@.fetch_status = 0
begin
select @.itemid,@.itemname
FETCH NEXT FROM Itemid_Cursor INTO @.itemid,@.itemname
end
close Itemid_Cursor
deallocate Itemid_Cursor
-- DROP TEST TABLE
drop table items
"uri@.bwayphoto.com" wrote:
> I can not assign more than one variable using this cursor:
>
> DECLARE Itemid_Cursor CURSOR FOR
> select distinct itemid,itemname from items
> OPEN Itemid_Cursor
> FETCH NEXT FROM Itemid_Cursor
> INTO @.itemid,@.itemname
>
multiple variables from query
Hi,
I want to assign multiple values from a query to local variables.
I can do it with a single one like:
SET @.Myvar = (SELECT FirstNameFROM People)
But if I want to select more then one field, I don't know how to do this.
SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't work.
I could do two queries, or write a cursor, but that sounds inefficient to
me,
Does anyone have a solution?
Thanks,
Leo
SELECT @.myVar = FirstName, @.MyVar2 = LastName FROM People
Jacco Schalkwijk
SQL Server MVP
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
|||Leo
Declare @.var1 INT, @.var2 INT
SELECT @.var1=col1,@.var2=col2 FROM Table
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
|||Hi,
You can write the query like the below,
Select @.Myvar = FirstName, @.Myvar2 = LastName FROM People
Thanks
Hari
MCDBA
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
|||Thank you for all the replies.
It works now!
Leo
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
I want to assign multiple values from a query to local variables.
I can do it with a single one like:
SET @.Myvar = (SELECT FirstNameFROM People)
But if I want to select more then one field, I don't know how to do this.
SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't work.
I could do two queries, or write a cursor, but that sounds inefficient to
me,
Does anyone have a solution?
Thanks,
Leo
SELECT @.myVar = FirstName, @.MyVar2 = LastName FROM People
Jacco Schalkwijk
SQL Server MVP
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
|||Leo
Declare @.var1 INT, @.var2 INT
SELECT @.var1=col1,@.var2=col2 FROM Table
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
|||Hi,
You can write the query like the below,
Select @.Myvar = FirstName, @.Myvar2 = LastName FROM People
Thanks
Hari
MCDBA
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
|||Thank you for all the replies.
It works now!
Leo
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
multiple variables from query
Hi,
I want to assign multiple values from a query to local variables.
I can do it with a single one like:
SET @.Myvar = (SELECT FirstNameFROM People)
But if I want to select more then one field, I don't know how to do this.
SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't work.
I could do two queries, or write a cursor, but that sounds inefficient to
me,
Does anyone have a solution?
Thanks,
LeoSELECT @.myVar = FirstName, @.MyVar2 = LastName FROM People
Jacco Schalkwijk
SQL Server MVP
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Leo
Declare @.var1 INT, @.var2 INT
SELECT @.var1=col1,@.var2=col2 FROM Table
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Hi,
You can write the query like the below,
Select @.Myvar = FirstName, @.Myvar2 = LastName FROM People
Thanks
Hari
MCDBA
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Thank you for all the replies.
It works now!
Leo
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>sql
I want to assign multiple values from a query to local variables.
I can do it with a single one like:
SET @.Myvar = (SELECT FirstNameFROM People)
But if I want to select more then one field, I don't know how to do this.
SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't work.
I could do two queries, or write a cursor, but that sounds inefficient to
me,
Does anyone have a solution?
Thanks,
LeoSELECT @.myVar = FirstName, @.MyVar2 = LastName FROM People
Jacco Schalkwijk
SQL Server MVP
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Leo
Declare @.var1 INT, @.var2 INT
SELECT @.var1=col1,@.var2=col2 FROM Table
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Hi,
You can write the query like the below,
Select @.Myvar = FirstName, @.Myvar2 = LastName FROM People
Thanks
Hari
MCDBA
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Thank you for all the replies.
It works now!
Leo
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>sql
multiple variables from query
Hi,
I want to assign multiple values from a query to local variables.
I can do it with a single one like:
SET @.Myvar = (SELECT FirstNameFROM People)
But if I want to select more then one field, I don't know how to do this.
SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't work.
I could do two queries, or write a cursor, but that sounds inefficient to
me,
Does anyone have a solution?
Thanks,
LeoSELECT @.myVar = FirstName, @.MyVar2 = LastName FROM People
--
Jacco Schalkwijk
SQL Server MVP
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Leo
Declare @.var1 INT, @.var2 INT
SELECT @.var1=col1,@.var2=col2 FROM Table
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Hi,
You can write the query like the below,
Select @.Myvar = FirstName, @.Myvar2 = LastName FROM People
Thanks
Hari
MCDBA
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Thank you for all the replies.
It works now!
Leo
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
I want to assign multiple values from a query to local variables.
I can do it with a single one like:
SET @.Myvar = (SELECT FirstNameFROM People)
But if I want to select more then one field, I don't know how to do this.
SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't work.
I could do two queries, or write a cursor, but that sounds inefficient to
me,
Does anyone have a solution?
Thanks,
LeoSELECT @.myVar = FirstName, @.MyVar2 = LastName FROM People
--
Jacco Schalkwijk
SQL Server MVP
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Leo
Declare @.var1 INT, @.var2 INT
SELECT @.var1=col1,@.var2=col2 FROM Table
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Hi,
You can write the query like the below,
Select @.Myvar = FirstName, @.Myvar2 = LastName FROM People
Thanks
Hari
MCDBA
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Thank you for all the replies.
It works now!
Leo
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
Multiple Variables Assigned To One Select
Hello,
Is there a way to assign multiple variables to one select statement as in the following example?
DECLARE @.FirstName VARCHAR(100)
DECLARE @.MiddleName VARCHAR(100)
DECLARE @.LastName VARCHAR(100)
@.FirstName, @.MiddleName, @.LastName = SELECT FirstName, MiddleName, LastName FROM USERS WHERE username='UniqueUserName'
I don't like having to use one select statement for each variable I need to pull from a query. This is in reference to a stored procedure.
Thank you!
Cody
Hi, you can use the below syntax.
SELECT
@.FirstName = FirstName,
@.MiddleName = MiddleName,
@.LastName = LastName
FROM USERS
WHERE username = 'UniqueUserName'
Eralper
http://www.kodyaz.com
Multiple variables
Hi
Our database has a series of tables that are all linked by a field called
company_code (amongst others). Occasionally data needs to be deleted and
this involves deleting multiple rows from some of these tables and updating
data in another table. I tend to wait until there are a few to do and then
do them all at once. To make the task less onerous I have saved a simple
script that does all the deletions for each code at once, a cut down version
is below:
/*Delete all pay data and make contact only*/
DECLARE @.code int
SET @.code = 12345
DELETEcompany_size
WHEREcompany_code = @.code
--Other deletions go in here
UPDATEcompany_basic
SETreport_status = null,
report_entry_urn = null,
next_rpt_archive = null,
/* Other fields to be updated... */
WHEREcompany_code = @.code
What I would like to be able to do is change the 'where' statements so that
they use the IN operator rather than the = operator. Is there a way to do
this using variables so I can assign a list of company codes to a variable
that can be used with an IN operator?
Thanks
Andy
On Wed, 13 Apr 2005 08:48:06 -0700, Andy wrote:
>Hi
>Our database has a series of tables that are all linked by a field called
>company_code (amongst others). Occasionally data needs to be deleted and
>this involves deleting multiple rows from some of these tables and updating
>data in another table. I tend to wait until there are a few to do and then
>do them all at once. To make the task less onerous I have saved a simple
>script that does all the deletions for each code at once, a cut down version
>is below:
>/*Delete all pay data and make contact only*/
>DECLARE @.code int
>SET @.code = 12345
>DELETEcompany_size
>WHEREcompany_code = @.code
>--Other deletions go in here
>UPDATEcompany_basic
>SETreport_status = null,
>report_entry_urn = null,
>next_rpt_archive = null,
>/* Other fields to be updated... */
>WHEREcompany_code = @.code
>What I would like to be able to do is change the 'where' statements so that
>they use the IN operator rather than the = operator. Is there a way to do
>this using variables so I can assign a list of company codes to a variable
>that can be used with an IN operator?
>Thanks
>Andy
>
Hi Andy,
To do that, you'll have to use dynamic SQL. Since this is in a script
that only you can execute, you don't have to worry about SQL Injection
in this case.
Rough outline:
/*Delete all pay data and make contact only*/
DECLARE @.codes nvarchar(40)
DECLARE @.SQL nvarchar(4000)
SET @.code = N'(12345,6789)'
SET @.sql = 'DELETEcompany_size
WHEREcompany_code IN ' + @.code
EXEC (@.sql)
--Other deletions go in here
SET @.sql = 'UPDATEcompany_basic
SETreport_status = null,
report_entry_urn = null,
next_rpt_archive = null,
/* Other fields to be updated... */
WHEREcompany_code IN ' + @.code
EXEC (@.sql)
In case you're tempted to use this technique in your production code as
well, read up on SQL injection and other dangers of this technique:
http://www.sommarskog.se/dynamic_sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo
Thanks very much for this - I've not tried it yet as I think there might be
a couple of typos that I want to confirm:
You declare a variable called @.codes, presumably all of my old instances of
@.code should now read @.codes instead.
Is the N in this line correct: SET @.code = N'(12345,6789)'?
I'm not brave enough to give it a go anyway!!!
Thanks again
"Hugo Kornelis" wrote:
> On Wed, 13 Apr 2005 08:48:06 -0700, Andy wrote:
>
> Hi Andy,
> To do that, you'll have to use dynamic SQL. Since this is in a script
> that only you can execute, you don't have to worry about SQL Injection
> in this case.
> Rough outline:
> /*Delete all pay data and make contact only*/
> DECLARE @.codes nvarchar(40)
> DECLARE @.SQL nvarchar(4000)
> SET @.code = N'(12345,6789)'
> SET @.sql = 'DELETEcompany_size
> WHEREcompany_code IN ' + @.code
> EXEC (@.sql)
> --Other deletions go in here
> SET @.sql = 'UPDATEcompany_basic
> SETreport_status = null,
> report_entry_urn = null,
> next_rpt_archive = null,
> /* Other fields to be updated... */
> WHEREcompany_code IN ' + @.code
> EXEC (@.sql)
>
> In case you're tempted to use this technique in your production code as
> well, read up on SQL injection and other dangers of this technique:
> http://www.sommarskog.se/dynamic_sql.html
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 14 Apr 2005 04:52:00 -0700, Andy wrote:
>Hi Hugo
>Thanks very much for this - I've not tried it yet as I think there might be
>a couple of typos that I want to confirm:
>You declare a variable called @.codes, presumably all of my old instances of
>@.code should now read @.codes instead.
Hi Andy,
My bad - since I adapted the script to work for more than one code at
once, I thought it'd be best to change the variable name from @.code to
@.codes - but after changing the first, I promptly forgot to change the
rest. <blush>
>Is the N in this line correct: SET @.code = N'(12345,6789)'?
Yes. I've changed the datatype to nvarchar (note: not just varchar, but
nvarchar), as is required for dynamic SQL. The N in fron of the string
constant means that this is also regarded as nchar data instead of plain
char data. (Nothing bad will happen if you leave out the N, nor if you
use character type varchar - but it will cuase SQL Server to do an
implicit conversion under the hood. I prefer to use explicit conversion,
or no conversion at all).
>I'm not brave enough to give it a go anyway!!!
Always test code suggestions on a test database. Make sure you have a
recent backup or another means to retore the data. And if possible,
enclose the code to be tested in a transaction, so that you can rollback
the changes if something goes awry.
Following the above advise for code you write yourself is not exactly a
bad idea either :-)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo
Thanks for this. I've been out of the office the last few days so have only
just picked this up. Thanks for the advice as well!
Andy
"Hugo Kornelis" wrote:
> On Thu, 14 Apr 2005 04:52:00 -0700, Andy wrote:
>
> Hi Andy,
> My bad - since I adapted the script to work for more than one code at
> once, I thought it'd be best to change the variable name from @.code to
> @.codes - but after changing the first, I promptly forgot to change the
> rest. <blush>
>
> Yes. I've changed the datatype to nvarchar (note: not just varchar, but
> nvarchar), as is required for dynamic SQL. The N in fron of the string
> constant means that this is also regarded as nchar data instead of plain
> char data. (Nothing bad will happen if you leave out the N, nor if you
> use character type varchar - but it will cuase SQL Server to do an
> implicit conversion under the hood. I prefer to use explicit conversion,
> or no conversion at all).
>
> Always test code suggestions on a test database. Make sure you have a
> recent backup or another means to retore the data. And if possible,
> enclose the code to be tested in a transaction, so that you can rollback
> the changes if something goes awry.
> Following the above advise for code you write yourself is not exactly a
> bad idea either :-)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||Hi Hugo
I'm getting a syntax error when running this script because oneof the fields
that gets updated is a char data type so:
--Update the various flags on the company basic table
SET @.sql = 'UPDATEcompany_basic
SETreport_status = null,
--Other fields being set to null
verify_svy_urn = null,
pay_or_contact = 'C',
year_end_date = null,
--Other fields being set to Null
WHEREcompany_code IN ' + @.codes
EXEC (@.sql)
As you can see it is because the quote before C is closing the string and so
on. Do I need to break the string at that point and concatenate it so that:
pay_or_contact = ' + 'C' + ', etc etc
or is there a way of getting SQL to ignore the string within the string?
Sorry I didn't include that bit in the initial question - I was trying to
cut down on the size of the post!
Thanks
Andy
"Hugo Kornelis" wrote:
> On Thu, 14 Apr 2005 04:52:00 -0700, Andy wrote:
>
> Hi Andy,
> My bad - since I adapted the script to work for more than one code at
> once, I thought it'd be best to change the variable name from @.code to
> @.codes - but after changing the first, I promptly forgot to change the
> rest. <blush>
>
> Yes. I've changed the datatype to nvarchar (note: not just varchar, but
> nvarchar), as is required for dynamic SQL. The N in fron of the string
> constant means that this is also regarded as nchar data instead of plain
> char data. (Nothing bad will happen if you leave out the N, nor if you
> use character type varchar - but it will cuase SQL Server to do an
> implicit conversion under the hood. I prefer to use explicit conversion,
> or no conversion at all).
>
> Always test code suggestions on a test database. Make sure you have a
> recent backup or another means to retore the data. And if possible,
> enclose the code to be tested in a transaction, so that you can rollback
> the changes if something goes awry.
> Following the above advise for code you write yourself is not exactly a
> bad idea either :-)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Mon, 18 Apr 2005 09:09:02 -0700, Andy wrote:
(snip)
>As you can see it is because the quote before C is closing the string and so
>on. Do I need to break the string at that point and concatenate it so that:
>pay_or_contact = ' + 'C' + ', etc etc
>or is there a way of getting SQL to ignore the string within the string?
(snip)
Hi Andy,
You'll have to make sure that you embed a quote within the string. The
code passed to the EXEC (@.sql) needs quotes around the C to recognise it
as a literal. There are two ways to do that: either use the ASCII value
for the quote, or double the quote (one quote in a string is considered
a string delimiter, two consecutive quotes are considered one quote as
part of the string):
--Update the various flags on the company basic table
SET @.sql = 'UPDATEcompany_basic
SETreport_status = null,
--Other fields being set to null
verify_svy_urn = null,
pay_or_contact = ''C'',
year_end_date = null,
--Other fields being set to Null
WHEREcompany_code IN ' + @.codes
EXEC (@.sql)
or
--Update the various flags on the company basic table
SET @.sql = 'UPDATEcompany_basic
SETreport_status = null,
--Other fields being set to null
verify_svy_urn = null,
pay_or_contact = ' +CHAR(39)+ 'C' +CHAR(39)+ ',
year_end_date = null,
--Other fields being set to Null
WHEREcompany_code IN ' + @.codes
EXEC (@.sql)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo
This has worked perfectly - its going to save me an awful lot of time!!!
Andy
"Hugo Kornelis" wrote:
> On Mon, 18 Apr 2005 09:09:02 -0700, Andy wrote:
> (snip)
> (snip)
> Hi Andy,
> You'll have to make sure that you embed a quote within the string. The
> code passed to the EXEC (@.sql) needs quotes around the C to recognise it
> as a literal. There are two ways to do that: either use the ASCII value
> for the quote, or double the quote (one quote in a string is considered
> a string delimiter, two consecutive quotes are considered one quote as
> part of the string):
> --Update the various flags on the company basic table
> SET @.sql = 'UPDATEcompany_basic
> SETreport_status = null,
> --Other fields being set to null
> verify_svy_urn = null,
> pay_or_contact = ''C'',
> year_end_date = null,
> --Other fields being set to Null
> WHEREcompany_code IN ' + @.codes
> EXEC (@.sql)
> or
> --Update the various flags on the company basic table
> SET @.sql = 'UPDATEcompany_basic
> SETreport_status = null,
> --Other fields being set to null
> verify_svy_urn = null,
> pay_or_contact = ' +CHAR(39)+ 'C' +CHAR(39)+ ',
> year_end_date = null,
> --Other fields being set to Null
> WHEREcompany_code IN ' + @.codes
> EXEC (@.sql)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
Our database has a series of tables that are all linked by a field called
company_code (amongst others). Occasionally data needs to be deleted and
this involves deleting multiple rows from some of these tables and updating
data in another table. I tend to wait until there are a few to do and then
do them all at once. To make the task less onerous I have saved a simple
script that does all the deletions for each code at once, a cut down version
is below:
/*Delete all pay data and make contact only*/
DECLARE @.code int
SET @.code = 12345
DELETEcompany_size
WHEREcompany_code = @.code
--Other deletions go in here
UPDATEcompany_basic
SETreport_status = null,
report_entry_urn = null,
next_rpt_archive = null,
/* Other fields to be updated... */
WHEREcompany_code = @.code
What I would like to be able to do is change the 'where' statements so that
they use the IN operator rather than the = operator. Is there a way to do
this using variables so I can assign a list of company codes to a variable
that can be used with an IN operator?
Thanks
Andy
On Wed, 13 Apr 2005 08:48:06 -0700, Andy wrote:
>Hi
>Our database has a series of tables that are all linked by a field called
>company_code (amongst others). Occasionally data needs to be deleted and
>this involves deleting multiple rows from some of these tables and updating
>data in another table. I tend to wait until there are a few to do and then
>do them all at once. To make the task less onerous I have saved a simple
>script that does all the deletions for each code at once, a cut down version
>is below:
>/*Delete all pay data and make contact only*/
>DECLARE @.code int
>SET @.code = 12345
>DELETEcompany_size
>WHEREcompany_code = @.code
>--Other deletions go in here
>UPDATEcompany_basic
>SETreport_status = null,
>report_entry_urn = null,
>next_rpt_archive = null,
>/* Other fields to be updated... */
>WHEREcompany_code = @.code
>What I would like to be able to do is change the 'where' statements so that
>they use the IN operator rather than the = operator. Is there a way to do
>this using variables so I can assign a list of company codes to a variable
>that can be used with an IN operator?
>Thanks
>Andy
>
Hi Andy,
To do that, you'll have to use dynamic SQL. Since this is in a script
that only you can execute, you don't have to worry about SQL Injection
in this case.
Rough outline:
/*Delete all pay data and make contact only*/
DECLARE @.codes nvarchar(40)
DECLARE @.SQL nvarchar(4000)
SET @.code = N'(12345,6789)'
SET @.sql = 'DELETEcompany_size
WHEREcompany_code IN ' + @.code
EXEC (@.sql)
--Other deletions go in here
SET @.sql = 'UPDATEcompany_basic
SETreport_status = null,
report_entry_urn = null,
next_rpt_archive = null,
/* Other fields to be updated... */
WHEREcompany_code IN ' + @.code
EXEC (@.sql)
In case you're tempted to use this technique in your production code as
well, read up on SQL injection and other dangers of this technique:
http://www.sommarskog.se/dynamic_sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo
Thanks very much for this - I've not tried it yet as I think there might be
a couple of typos that I want to confirm:
You declare a variable called @.codes, presumably all of my old instances of
@.code should now read @.codes instead.
Is the N in this line correct: SET @.code = N'(12345,6789)'?
I'm not brave enough to give it a go anyway!!!
Thanks again
"Hugo Kornelis" wrote:
> On Wed, 13 Apr 2005 08:48:06 -0700, Andy wrote:
>
> Hi Andy,
> To do that, you'll have to use dynamic SQL. Since this is in a script
> that only you can execute, you don't have to worry about SQL Injection
> in this case.
> Rough outline:
> /*Delete all pay data and make contact only*/
> DECLARE @.codes nvarchar(40)
> DECLARE @.SQL nvarchar(4000)
> SET @.code = N'(12345,6789)'
> SET @.sql = 'DELETEcompany_size
> WHEREcompany_code IN ' + @.code
> EXEC (@.sql)
> --Other deletions go in here
> SET @.sql = 'UPDATEcompany_basic
> SETreport_status = null,
> report_entry_urn = null,
> next_rpt_archive = null,
> /* Other fields to be updated... */
> WHEREcompany_code IN ' + @.code
> EXEC (@.sql)
>
> In case you're tempted to use this technique in your production code as
> well, read up on SQL injection and other dangers of this technique:
> http://www.sommarskog.se/dynamic_sql.html
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 14 Apr 2005 04:52:00 -0700, Andy wrote:
>Hi Hugo
>Thanks very much for this - I've not tried it yet as I think there might be
>a couple of typos that I want to confirm:
>You declare a variable called @.codes, presumably all of my old instances of
>@.code should now read @.codes instead.
Hi Andy,
My bad - since I adapted the script to work for more than one code at
once, I thought it'd be best to change the variable name from @.code to
@.codes - but after changing the first, I promptly forgot to change the
rest. <blush>
>Is the N in this line correct: SET @.code = N'(12345,6789)'?
Yes. I've changed the datatype to nvarchar (note: not just varchar, but
nvarchar), as is required for dynamic SQL. The N in fron of the string
constant means that this is also regarded as nchar data instead of plain
char data. (Nothing bad will happen if you leave out the N, nor if you
use character type varchar - but it will cuase SQL Server to do an
implicit conversion under the hood. I prefer to use explicit conversion,
or no conversion at all).
>I'm not brave enough to give it a go anyway!!!
Always test code suggestions on a test database. Make sure you have a
recent backup or another means to retore the data. And if possible,
enclose the code to be tested in a transaction, so that you can rollback
the changes if something goes awry.
Following the above advise for code you write yourself is not exactly a
bad idea either :-)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo
Thanks for this. I've been out of the office the last few days so have only
just picked this up. Thanks for the advice as well!
Andy
"Hugo Kornelis" wrote:
> On Thu, 14 Apr 2005 04:52:00 -0700, Andy wrote:
>
> Hi Andy,
> My bad - since I adapted the script to work for more than one code at
> once, I thought it'd be best to change the variable name from @.code to
> @.codes - but after changing the first, I promptly forgot to change the
> rest. <blush>
>
> Yes. I've changed the datatype to nvarchar (note: not just varchar, but
> nvarchar), as is required for dynamic SQL. The N in fron of the string
> constant means that this is also regarded as nchar data instead of plain
> char data. (Nothing bad will happen if you leave out the N, nor if you
> use character type varchar - but it will cuase SQL Server to do an
> implicit conversion under the hood. I prefer to use explicit conversion,
> or no conversion at all).
>
> Always test code suggestions on a test database. Make sure you have a
> recent backup or another means to retore the data. And if possible,
> enclose the code to be tested in a transaction, so that you can rollback
> the changes if something goes awry.
> Following the above advise for code you write yourself is not exactly a
> bad idea either :-)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||Hi Hugo
I'm getting a syntax error when running this script because oneof the fields
that gets updated is a char data type so:
--Update the various flags on the company basic table
SET @.sql = 'UPDATEcompany_basic
SETreport_status = null,
--Other fields being set to null
verify_svy_urn = null,
pay_or_contact = 'C',
year_end_date = null,
--Other fields being set to Null
WHEREcompany_code IN ' + @.codes
EXEC (@.sql)
As you can see it is because the quote before C is closing the string and so
on. Do I need to break the string at that point and concatenate it so that:
pay_or_contact = ' + 'C' + ', etc etc
or is there a way of getting SQL to ignore the string within the string?
Sorry I didn't include that bit in the initial question - I was trying to
cut down on the size of the post!
Thanks
Andy
"Hugo Kornelis" wrote:
> On Thu, 14 Apr 2005 04:52:00 -0700, Andy wrote:
>
> Hi Andy,
> My bad - since I adapted the script to work for more than one code at
> once, I thought it'd be best to change the variable name from @.code to
> @.codes - but after changing the first, I promptly forgot to change the
> rest. <blush>
>
> Yes. I've changed the datatype to nvarchar (note: not just varchar, but
> nvarchar), as is required for dynamic SQL. The N in fron of the string
> constant means that this is also regarded as nchar data instead of plain
> char data. (Nothing bad will happen if you leave out the N, nor if you
> use character type varchar - but it will cuase SQL Server to do an
> implicit conversion under the hood. I prefer to use explicit conversion,
> or no conversion at all).
>
> Always test code suggestions on a test database. Make sure you have a
> recent backup or another means to retore the data. And if possible,
> enclose the code to be tested in a transaction, so that you can rollback
> the changes if something goes awry.
> Following the above advise for code you write yourself is not exactly a
> bad idea either :-)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Mon, 18 Apr 2005 09:09:02 -0700, Andy wrote:
(snip)
>As you can see it is because the quote before C is closing the string and so
>on. Do I need to break the string at that point and concatenate it so that:
>pay_or_contact = ' + 'C' + ', etc etc
>or is there a way of getting SQL to ignore the string within the string?
(snip)
Hi Andy,
You'll have to make sure that you embed a quote within the string. The
code passed to the EXEC (@.sql) needs quotes around the C to recognise it
as a literal. There are two ways to do that: either use the ASCII value
for the quote, or double the quote (one quote in a string is considered
a string delimiter, two consecutive quotes are considered one quote as
part of the string):
--Update the various flags on the company basic table
SET @.sql = 'UPDATEcompany_basic
SETreport_status = null,
--Other fields being set to null
verify_svy_urn = null,
pay_or_contact = ''C'',
year_end_date = null,
--Other fields being set to Null
WHEREcompany_code IN ' + @.codes
EXEC (@.sql)
or
--Update the various flags on the company basic table
SET @.sql = 'UPDATEcompany_basic
SETreport_status = null,
--Other fields being set to null
verify_svy_urn = null,
pay_or_contact = ' +CHAR(39)+ 'C' +CHAR(39)+ ',
year_end_date = null,
--Other fields being set to Null
WHEREcompany_code IN ' + @.codes
EXEC (@.sql)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo
This has worked perfectly - its going to save me an awful lot of time!!!
Andy
"Hugo Kornelis" wrote:
> On Mon, 18 Apr 2005 09:09:02 -0700, Andy wrote:
> (snip)
> (snip)
> Hi Andy,
> You'll have to make sure that you embed a quote within the string. The
> code passed to the EXEC (@.sql) needs quotes around the C to recognise it
> as a literal. There are two ways to do that: either use the ASCII value
> for the quote, or double the quote (one quote in a string is considered
> a string delimiter, two consecutive quotes are considered one quote as
> part of the string):
> --Update the various flags on the company basic table
> SET @.sql = 'UPDATEcompany_basic
> SETreport_status = null,
> --Other fields being set to null
> verify_svy_urn = null,
> pay_or_contact = ''C'',
> year_end_date = null,
> --Other fields being set to Null
> WHEREcompany_code IN ' + @.codes
> EXEC (@.sql)
> or
> --Update the various flags on the company basic table
> SET @.sql = 'UPDATEcompany_basic
> SETreport_status = null,
> --Other fields being set to null
> verify_svy_urn = null,
> pay_or_contact = ' +CHAR(39)+ 'C' +CHAR(39)+ ',
> year_end_date = null,
> --Other fields being set to Null
> WHEREcompany_code IN ' + @.codes
> EXEC (@.sql)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
Wednesday, March 7, 2012
multiple SQL commands at once
I'm running asp.net 2.0 and acessing MSSQL 2K. I am trying to run a query in which I need to set up variables first. I tried the following group of commands as shown, passing it to the SqlDataReader object, but it failed.. does anyone know how i can pass multiple SQL commands?
MainQuery = "declare @.MinGrades as Table(GradeID Bigint) " & _
" INSERT @.MinGrades SELECT MIN(CreditGrades.SplitID) AS Expr1" & _
" FROM CreditGrades INNER JOIN" & _
" CreditGradeSplits ON CreditGradeSplits.CreditGradeSplitID = CreditGrades.SplitID " & _
" WHERE (CreditGrades.x0x30 = - 1 OR " & _
" CreditGrades.x0x30 >= 0) AND (CreditGrades.x1x30 = - 1 OR " & _
" CreditGrades.x1x30 >= 0) AND (CreditGrades.x2x30 = - 1 OR " & _
" CreditGrades.x2x30 >= 0) AND (CreditGrades.x3x30 = - 1 OR " & _
" CreditGrades.x3xNOD >= 0) " & _
" GROUP BY CreditGradeSplits.CreditGradeGroupID" & _
" Select * from @.MinGrades "Looks good to me. What error are you getting? Please copy/paste the stack trace if possible as well.|||
Looks good to me. What error are you getting? Please copy/paste the stack trace if possible as well.
I suspect you realize that you don't really need to do that query in 3 commands, and can combine them into a single select.
Subscribe to:
Posts (Atom)