Executing SP for all results

I would like to have my stored procedure executed for each item returned by
a Select query
Ex.
Declare @.file_id varchar(5)
Select @.file_id = file_id from GEN where this_value <> ''
execute sp_mystored_procedure @.file_id
go
Currently it only works for one record
How do I get it to do it for all returned records?
The top select statement may return 1000 records, but only one pass is made
through the stored procedure
Hints Please
Thanks
Darrell
darrellp@.btmcpa.comLooping through resultsets with cursors is usually a bad idea and there
is normally a better set-based solution. Can you post your proc, your
select query and some schema so we can advise on a better approach
(maybe some test data & expected results too)?
*mike hodgson*
http://sqlnerd.blogspot.com
Doc Parker wrote:

>I would like to have my stored procedure executed for each item returned by
>a Select query
>
>Ex.
>Declare @.file_id varchar(5)
>Select @.file_id = file_id from GEN where this_value <> ''
>execute sp_mystored_procedure @.file_id
>go
>Currently it only works for one record
>How do I get it to do it for all returned records?
>The top select statement may return 1000 records, but only one pass is made
>through the stored procedure
>Hints Please
>Thanks
>Darrell
>darrellp@.btmcpa.com
>
>
>|||The procedure in question actually runs as part of an exsiting TRIGGER. I am
installing this TRIGGER into a table of a database and want to call it in o
rder to extract data all ready in the table. Otherwise it works great for re
cords currently being added. If I were to use a CURSOR to run this SP it wou
ld be a one time deal. Nothing permanent.
Thanks
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:uWo%23nF6JGHA.340
8@.TK2MSFTNGP12.phx.gbl...
Looping through resultsets with cursors is usually a bad idea and there is n
ormally a better set-based solution. Can you post your proc, your select qu
ery and some schema so we can advise on a better approach (maybe some test d
ata & expected results too)?
mike hodgson
http://sqlnerd.blogspot.com
Doc Parker wrote:
I would like to have my stored procedure executed for each item returned by
a Select query
Ex.
Declare @.file_id varchar(5)
Select @.file_id = file_id from GEN where this_value <> ''
execute sp_mystored_procedure @.file_id
go
Currently it only works for one record
How do I get it to do it for all returned records?
The top select statement may return 1000 records, but only one pass is made
through the stored procedure
Hints Please
Thanks
Darrell
darrellp@.btmcpa.com

Executing osql commands through batch file

Hello

I have a script ,which runs with osql

The script is :

osql -E
declare @.cmd nvarchar(1000)
declare @.cmd2 nvarchar(1000)
declare @.state1 varchar(100)
declare @.message varchar(100)
set @.message = ''
-- Build command to determine state of SQLSERVERAGENT service on Master Server
SET @.CMD = 'create table #state (state varchar(2000))' + char(10) +
'declare @.cmdx varchar(1000)' + char(10) +
'insert into #state EXEC master..xp_servicecontrol ''''QueryState'''', ''''SQLSERVERAGENT'' +
+ char(10) + 'select @.state=state from #state' + char(10) +
'drop table #state'
-- Build command to execute command that determines state of service being monitored
set @.cmd2 = 'declare @.state varchar(100)' + char(10) +
'exec ' + rtrim(@.@.servername) + '.master.dbo.sp_executesql N''' + @.CMD + ''',' +
'N''@.state varchar(100) out'',' +
'@.state out' + char(10) +
'set @.state1 = @.state'
-- Execute command and return state of service being monitored
exec master.dbo.sp_executesql @.cmd2,N'@.state1 varchar(100) out',@.state1 out
-- Is the service that was monitored not
IF (UPPER(@.state1) <> 'RUNNING.')
--if @.state1 <1 'Running.'
begin
-- Display message that primary monitor is down
select @.message = @.message+char(13)+ @.@.servername + ' -' + 'Sql Server Agent Not Running'+char(13)
print 'Master server "' + rtrim(@.@.servername) + '" for monitoring is not available.'
exec master.dbo.xp_smtp_sendmail

It works fine when I run it on the command line prompt.
And I receive a mail , if the server agent is running.

But when I save it as bat file and try to run , it stops and doesnot even give an error.

Can anyone let me know what I can do.

ThanksHello

I have got it working.

Just I need to use :

osql -E -iC:\serveragent.sql -oC:\outputfile.txt

Thanks

Executing a variable inside a stored procedure

Hello :) I need to do something like this:

CREATE PROCEDURE SelectCostumers @.name varchar(100)

Declare @.SQL = "SELECT Id, Name FROM Costumers"

AS

IF (@.name IS NULL)

@.SQL

ELSE

@.SQL += "WHERE Name LIKE @.name"

See, what I need is a string variable that I can concatenate with whatever I want depending on the parameter I get.

Thank you

Hi,

Try as below.

CREATE PROCEDURE dbo.SelectCostumers
@.name varchar(100)

AS
BEGIN


DECLARE @.SQL NVARCHAR(500)
SET @.SQL = N'SELECT CustomerID, ContactName FROM Customers'


IF (@.name IS NULL)
BEGIN
EXEC sp_executesql @.SQL
END
ELSE
BEGIN
SET @.SQL = @.SQL + ' WHERE ContactName=''' + @.name + ''''
EXEC sp_executesql @.SQL
END

END

HTH

|||Thank You!!

Executing a dynamic query without using sp_executeSql

Hi,
DECLARE @.PrdID VARCHAR (50)
SET @.PrdID = '1,2'
SELECT @.PrdID
SELECT ProductName FROM Product WHERE PtoductID IN (@.PrdID)
Where the data type of ProductCode is Int and ProductName is Varchar
When I executing this query I gets null even if the product table contains
product code 1 and 2 . and productName is not null.
Any one know how can execute this query without using Sp_ExecuteSql ?Hello, Shahi
See this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html
Razvan

Executing a dynamic query without using sp_executeSql

Hi,
DECLARE @.PrdID VARCHAR (50)
SET @.PrdID = '1,2'
SELECT @.PrdID
SELECT ProductName FROM Product WHERE PtoductID IN (@.PrdID)
Where the data type of ProductCode is Int and ProductName is Varchar
When I executing this query I gets null even if the product table contains
product code 1 and 2 . and productName is not null.
Any one know how can execute this query without using Sp_ExecuteSql ?
Hello, Shahi
See this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html
Razvan

Executing a dynamic query without using sp_executeSql

Hi,
DECLARE @.PrdID VARCHAR (50)
SET @.PrdID = '1,2'
SELECT @.PrdID
SELECT ProductName FROM Product WHERE PtoductID IN (@.PrdID)
Where the data type of ProductCode is Int and ProductName is Varchar
When I executing this query I gets null even if the product table contains
product code 1 and 2 . and productName is not null.
Any one know how can execute this query without using Sp_ExecuteSql ?Hello, Shahi
See this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html
Razvan

Execute SQL Task question

I need to get the curent date from within a Execute SQL Task step,

I tried this

Declare @.Date as datetime
set @.Date = SELECT GETDATE()

..and this

Declare @.Date as datetime

set @.Date = Date

...without any luck.

I need the date as mm/dd/yyyy, anyone know how?

Thanks,
JavawabaSELECT CONVERT(varchar, GETDATE(), 101)

Greg.|||

Hello,

You can write a query like : "select convert(varchar,getdate(),101) as r_currentdt" and assign this result to some variable in the result mapping. Note:R_currentdt is a resultset name which you have to assign to some variable of the type string and can use any where within the scope.

Regards,

Raju

Execute SQL task

I want to perform following using Execute SQL task

declare @.LogID int, @.OperationID int

exec usp_CreateLog

@.DataProvider='ABC'

,@.Source_Original = ''

,@.NoTables = 3

,@.UserName = '@.userName'

,@.LogID =@.LogID output

select @.LogID as LogID

I want @.LogID as output. Please suggest how to achive in SSIS

Use OLEDB COmmand, ans in the SQL Command write EXEC SP_Name ?,?,?,?,? OUTPUT

And in the second tab link the input/output columns

Regards!

|||

Can you please explain in detail as I am new in SSIS.

EXEC SP_Name ?,?,?,?,? OUTPUT

What is ? ?

|||

can you please give more detail as am new in SSIS

what is ? in the sql statement... considering my sql statement.

|||

This is your stored procedure that you can create in your database.

I didnt tested yet, but is more or less this:

Code Snippet

CREATE PROCEDURE usp_CreateLog

@.MyDataProvider char(2),

@.MySource_Original varchar(20),

@.MyNoTables int,

@.MyUserName char(10)

AS

DECLARE @.LogID int, @.OperationID int

INSERT INTO [MyTable] (DataProvider, SourceOriginal, NoTables, Username)

VALUES (@.MyDataProvider, @.MySource_Original, @.MyNoTables, @.MyUername)

SET @.LogID= SCOPE_IDENTITY()

RETURN @.LogID

Now, create an OLEDB Command to execute it.

In the second tab of OLEDB Command, link the input and output columns, and use your own variables [@.@.User::] or system variables [@.@.system::]

I can give some images if you want.

Regards!

|||

dont worry, I willl help you...

If you need I can give some images!

Regards!

|||

thanks for detail. It works

Can u please give me one more help

I want to execute following function with parameter LogId

like

set @.Dest= dbo.fn_GetArchiveFileName (?)

And I want @.Dest as output string

Can u please help me in this

|||

Explain me better...

Where you need to execute the function? In database? In the controlFlow? Dataflow?

Give me more details!

Regards!

Execute Sql Statement Stored in Table Type Variable

hi,

I Wrote one stored procedure in which i declare one variable as Table
then i stored in that table type variable
select
*
from
emp
so now my table contain one sql statement by 4 rows
now i want to execute the sql statement how (MS SQL SERVER 2000)

Actually my Query Contain more than 15,000 characters.hi
Just In the end of your Storedprodure type
print @.your SQl Statment|||

Quote:

Originally Posted by hisham123

hi,

I Wrote one stored procedure in which i declare one variable as Table
then i stored in that table type variable
select
*
from
emp
so now my table contain one sql statement by 4 rows
now i want to execute the sql statement how (MS SQL SERVER 2000)

Actually my Query Contain more than 15,000 characters.

Hi,
select the four rows in to four variables @.a,@.b,@.c,@.d,
remember while assigning you have to put the row values in single quotes
then execute the query by using this technic
[CODE]
exec(@.a+' '+@.b+' '+@.c+' '+@.d)

execute problem

I whan to run this code, but it still doesn't work. :(
DECLARE @.MyText VARCHAR(1000)
SET @.MyText = 'SET IDENTITY_INSERT [Table1] ON ' + CHAR(13) + ' GO'
EXEC(@.MyText)
MSSQL doesn't like the "GO"
ThanksWhy do you need the GO?

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
GO

DECLARE @.mySQL99 VARCHAR(1000)
SET @.mySQL99 = 'SET IDENTITY_INSERT myTable99 ON INSERT INTO myTable99(Col1,Col2) SELECT 1,''A'''
+ ' SELECT * FROM myTable99'
SELECT @.mySQL99
EXEC(@.mySQL99)
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

And why would you do dynamic sql for this anyway?