How to exec a SQL in stored procedure?

For example,
I have a variable @.SQL = 'Select ''Hello World'''
How can I execute this @.SQL in stored procedure?
Sorry for newbie question =]
Thank you.EXEC (@.Sql)
However, be aware that "Hello World
"Cylix" <cylix2000@.gmail.com> wrote in message
news:1155862466.120172.203660@.i3g2000cwc.googlegroups.com...
> For example,
> I have a variable @.SQL = 'Select ''Hello World'''
> How can I execute this @.SQL in stored procedure?
> Sorry for newbie question =]
> Thank you.
>|||The exact same way. (Just a little issue with quotes...)
I suggest that you review this article before embarking into using dynamic S
QL.
http://www.sommarskog.se/dynamic_sql.html
Example:
CREATE PROCEDURE #MyTest
AS
BEGIN
DECLARE @.SQL nvarchar(100)
SET @.SQL = 'SELECT ''Hello, World.'''
EXECUTE sp_executesql @.SQL
END
GO
EXECUTE #MyTest
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Cylix" <cylix2000@.gmail.com> wrote in message news:1155862466.120172.203660@.i3g2000cwc.goog
legroups.com...
> For example,
> I have a variable @.SQL = 'Select ''Hello World'''
> How can I execute this @.SQL in stored procedure?
>
> Sorry for newbie question =]
> Thank you.
>sql

How to exec a SQL in stored procedure?

For example,
I have a variable @.SQL = 'Select ''Hello World'''
How can I execute this @.SQL in stored procedure?
Sorry for newbie question =]
Thank you.EXEC (@.Sql)
However, be aware that "Hello World
"Cylix" <cylix2000@.gmail.com> wrote in message
news:1155862466.120172.203660@.i3g2000cwc.googlegroups.com...
> For example,
> I have a variable @.SQL = 'Select ''Hello World'''
> How can I execute this @.SQL in stored procedure?
> Sorry for newbie question =]
> Thank you.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0BBE_01C6C228.19951980
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
The exact same way. (Just a little issue with quotes...)
I suggest that you review this article before embarking into using =dynamic SQL.
http://www.sommarskog.se/dynamic_sql.html=20
Example:
CREATE PROCEDURE #MyTest
AS
BEGIN
DECLARE @.SQL nvarchar(100)
SET @.SQL =3D 'SELECT ''Hello, World.'''
EXECUTE sp_executesql @.SQL
END
GO
EXECUTE #MyTest
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"Cylix" <cylix2000@.gmail.com> wrote in message =news:1155862466.120172.203660@.i3g2000cwc.googlegroups.com...
> For example,
> I have a variable @.SQL =3D 'Select ''Hello World'''
> How can I execute this @.SQL in stored procedure?
> > Sorry for newbie question =3D]
> Thank you.
>
--=_NextPart_000_0BBE_01C6C228.19951980
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

The exact same way. (Just a little =issue with quotes...)
I suggest that you review this article =before embarking into using dynamic SQL.
http://www.sommarskog.se/dynamic_sql.html">http://www.sommarskog.=se/dynamic_sql.html
Example:
CREATE PROCEDURE #MyTestAS BEGIN
=DECLARE @.SQL nvarchar(100)
=SET @.SQL =3D 'SELECT ''Hello, World.'''
=EXECUTE sp_executesql @.SQL
=ENDGO
EXECUTE #MyTest
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"Cylix" =wrote in message news:1155862466.120172.203660@.i3g2000cwc.googlegroups.com=...> =For example,> I have a variable @.SQL =3D 'Select ''Hello =World'''> How can I execute this @.SQL in stored procedure?> > Sorry for =newbie question =3D]> Thank you.>

---

--=_NextPart_000_0BBE_01C6C228.19951980--

How to eval() a variable to get a column name?

OK.. I've got a stored procedure I'm writing, which accepts an argument called @.statfield... let's say I want to use this variable as a literal part of a SQL statement, example:

select * from table1 where @.statfield = @.value

I want to do basically an eval(@.statfield) so if @.statfield is "key_id", then the select statement comes out:

select * from table1 where key_id = @.value

How can I do this?

Thanks!Originally posted by MDesigner
OK.. I've got a stored procedure I'm writing, which accepts an argument called @.statfield... let's say I want to use this variable as a literal part of a SQL statement, example:

select * from table1 where @.statfield = @.value

I want to do basically an eval(@.statfield) so if @.statfield is "key_id", then the select statement comes out:

select * from table1 where key_id = @.value

How can I do this?

Thanks!

One way would be to build dynamic sql and execute it

I copy/pasted the following from SQL Server help

Building Statements at Run Time

DECLARE @.SQLString NVARCHAR(500)

/* Set column list. CHAR(13) is a carriage return, line feed.*/
SET @.SQLString = N'SELECT FirstName, LastName, Title' + CHAR(13)

/* Set FROM clause with carriage return, line feed. */
SET @.SQLString = @.SQLString + N'FROM Employees' + CHAR(13)

/* Set WHERE clause. */
SET @.SQLString = @.SQLString + N'WHERE LastName LIKE ''D%'''

EXEC sp_executesql @.SQLString|||One problem:

my sql statement is:

select distinct @.stat = packing_shipping from cp_elements where campaign_id = 10

however, if I use execlsql to execute that, @.stat is in some kind of local scope...and is asking to be declared, even though it already is.

How do I get my @.stat return value?? I can't do

select @.stat = exec sp_executesql @.sql

nor this:

exec @.stat = sp_executesql @.sql

help!|||declare @.stat <data type>
exec sp_executesql @.sql, N'@.stat <data type> out', @.stat out

print @.stat|||Hm, that didn't work for some reason..

declare @.stat int

....

set @.sql = N'select distinct ' + @.statfield + N' from cp_elements where campaign_id = ' + convert(nvarchar, @.campaign_id)
exec sp_executesql @.sql, N'@.stat int out', @.stat out
set @.rc = @.@.rowcount
select @.stat

@.stat shows up as NULL for some reason. did I do something wrong here?|||nevermind. altered the SQL and it worked:

set @.sql = N'select distinct @.stat = ' + @.statfield + N' from cp_elements where campaign_id = ' + convert(nvarchar, @.campaign_id)

How to escape out single quotes from an T-SQL variable

How do I escape out the single quotes in the T-SQL Variable Assignment
appearing below around the comma for setting the FIELDTERMINATOR and the \n
setting the ROWTERMINATOR?
SET @.sql = ' BULK INSERT sandbox.dbo.core_edi FROM ' + @.full_filename + '
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )'
Thanks,
DavidTranslate each single quote into two single quotes:
SET @.sql = ' BULK INSERT sandbox.dbo.core_edi FROM ' + @.full_filename + '
WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'' )'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"David Bowles" <DavidBowles@.discussions.microsoft.com> wrote in message
news:B423BD8F-F0A8-4516-B720-C8990908BD1D@.microsoft.com...
How do I escape out the single quotes in the T-SQL Variable Assignment
appearing below around the comma for setting the FIELDTERMINATOR and the \n
setting the ROWTERMINATOR?
SET @.sql = ' BULK INSERT sandbox.dbo.core_edi FROM ' + @.full_filename + '
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )'
Thanks,
David|||David,
Double them.
SET @.sql = ' BULK INSERT sandbox.dbo.core_edi FROM ' + @.full_filename + '
WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'' )'
AMB
"David Bowles" wrote:

> How do I escape out the single quotes in the T-SQL Variable Assignment
> appearing below around the comma for setting the FIELDTERMINATOR and the \
n
> setting the ROWTERMINATOR?
> SET @.sql = ' BULK INSERT sandbox.dbo.core_edi FROM ' + @.full_filename + '
> WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )'
> Thanks,
> David|||By using two single quotes.
PRINT 'O''Leary';
"David Bowles" <DavidBowles@.discussions.microsoft.com> wrote in message
news:B423BD8F-F0A8-4516-B720-C8990908BD1D@.microsoft.com...
> How do I escape out the single quotes in the T-SQL Variable Assignment
> appearing below around the comma for setting the FIELDTERMINATOR and the
> \n
> setting the ROWTERMINATOR?
> SET @.sql = ' BULK INSERT sandbox.dbo.core_edi FROM ' + @.full_filename + '
> WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )'
> Thanks,
> David

How to dynamically map the coloumn to a flat file destination?

Hi All,

I am struck at one point. I am trying to this operation and not able to go further.

1. I have got the dataset to a variable in the control flow.

2. I am looping through the dataset based on a coloumn.

3. Now inside my For each loop i have a dataflow task.

4. In the data flow task i am trying to build a dynamic query using the OLEDB Source and i have selected SQL Command from variable. And the variable build the Query as select * from @.othervariable.

Now my question is

Can i send the data of each of the Query resultset to an out put text file using Flat File Connection? If yes pls guide me how? I have tried to create a flat file connection but i am failing how to map the data comming from step 4 dynamically for every query, since every query gives you a different resultset with different coloumns.

Thanks in advance..

Regards,

Dev.

Metadata cannot change. Will it change with your dynamic query?|||

hi phil,

yes every query produces a different result. pls. let me know the best way to handle this kind of scenario. I have total 30 different result sets and need to generate 30 different flat files based on the different out out.

Thanx in advance..

Dev

|||

Dev,

If the datasets has the same structure (column names, data types); then it is possible to do it using SSIS.

I have 2 posts in my blog that explains how to loop through a a data set and how write into different Excel files. You could modified to write into different flat files if you need.

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

|||

Rafeal,

Unfortunately the datasets are not same. they are different in each case. I am not bale to figure out a best solution for this. Pls let me know if u have any ideas onthis...

thanx in advance..

dev

|||

dev15`4534345677 wrote:

Rafeal,

Unfortunately the datasets are not same. they are different in each case. I am not bale to figure out a best solution for this. Pls let me know if u have any ideas onthis...

thanx in advance..

dev

You're going to have to setup a data flow for each format.|||

I completly agree with your approch as that was my last option. Do u think this is the only one i have.....

Can't i do any thing dynamically as u can imagine doing 30 different data flow for 30 times !!!!

Thanx in advance..

Regards,

Dev

|||

dev15`4534345677 wrote:

I completly agree with your approch as that was my last option. Do u think this is the only one i have.....

Can't i do any thing dynamically as u can imagine doing 30 different data flow for 30 times !!!!

Thanx in advance..

Regards,

Dev

No, that is your option. SSIS needs to know the metadata up front. Sorry. How would you map the columns in the destination anyway, if you want it to be dynamic?

You might be able to write a program that programatically builds a data flow for you, but then you aren't really using SSIS at that point. |||

Phil,

what would be your approach if there is a scenario like this for you.....

Regards,

Dev

|||

dev15`4534345677 wrote:

Phil,

what would be your approach if there is a scenario like this for you.....

Regards,

Dev

Well, the only other option that comes to mind is to read everything in as one big string, and then use a conditional split to send the data to 30 different connection managers. (That's an overly-simplified explanation, but I hope you get the idea...)

The connection managers are going to have to be setup for each format. I'm not sure there's a way around that.|||

Hi All,

I would appreciate If any body has a different solution for this pls. post it here...

regds,

dev

|||

dev15`4534345677 wrote:

Hi All,

I would appreciate If any body has a different solution for this pls. post it here...

regds,

dev

Also please search this forum. You are not the first to desire this and you'll not be the last.

The current version is very strict on metadata rules. Perhaps SSIS isn't the best option? That is, maybe you should write views and then use another SQL utility to output the results to a file. BCP, perhaps.|||Building package programatically may be another option...more complex perhaps. BOL it a godd point ot start|||

I apologize in advance if I misinterpreted something but wouldn't a script task be an option? Theoretically you could replace your data flow task with a script task and pass in the variable containing the data flow then within the script task you could loop through the dataset columns and rows and output it to a flat file of your chosing. Again, I apologize if a script task is not an option but I do not see it being overly difficult to implement what you are asking using one. Have you looked into this option? Are you familar with VB.NET?

|||

ADMariner wrote:

I apologize in advance if I misinterpreted something but wouldn't a script task be an option? Theoretically you could replace your data flow task with a script task and pass in the variable containing the data flow then within the script task you could loop through the dataset columns and rows and output it to a flat file of your chosing. Again, I apologize if a script task is not an option but I do not see it being overly difficult to implement what you are asking using one. Have you looked into this option? Are you familar with VB.NET?

I don't think it's an option as you still need to know the outputs in advance to setup the script task's outputs.

how to do an addYears(-2) in T-SQL

I have a variable @.yr which represents the years column in my table, and there is also a Months column, which may or may not have all the months in each year, so I'm trying to get the # of months in a 2 year period - - -

Heres' where I'm stuck:

DECLARE @.yr AS Int

SELECT count(*) AS cnt, month, year
FROM Sales_history
WHERE year BETWEEN @.yr AND @.yr (addyears(- 2))

I put the addyears part because I know you can work with stuff like that in VB.net - - - -but I don't know how to construct this in T-SQL - - help!!

The T-SQL function you're looking for is DATEADD.

http://msdn2.microsoft.com/en-US/library/ms186819.aspx

So, DATEADD(year, -2, @.yr).|||I think I read your post too fast though. DATEADD needs a date for that third parameter. Since @.yr is an int already, why don't you just do your subtraction there. If you need mid year ranges (instead of January to December), you will most likely need to compose a date from your month/year data.|||

As Hous68 said, you can use DATEADD function in T-SQL. But if the year column in the table is INT, why not directly compare it with @.yr:year BETWEEN @.yr-2 AND @.yr ? If theyearcolumn is DATETIME, and since you only what to compare the year part of the datetime to @.yr, you can use YEAR() function to get th year part for comparation: YEAR(year) BETWEEN @.yr-2 AND @.yr .

|||

SELECT count(*) AS cnt, month, year
FROM Sales_history
WHERE year BETWEEN @.yr AND @.yr-2

GROUP BY month,year