In Stored Procedure How To Loop Through Rows In Table And Pass Parameter To EXEC SP

I have a temporary table with multiple records and a Stored Procedure requiring a value. In a Stored Procedure, I want to loop through records in the table and use the value from each record read as input to another Stored Procedure. How do I do this?

View Replies


How Do You Pass A Parameter To A Stored Procedure

How can I pass a parameter to a stored procedure using Visual Web Developer 2005?  I have created a SQLDataSource that calls the SP. 

View Replies View Related

Can't Pass 0 In Stored Procedure Parameter

Hi I have an if clause in my code to add the final parameter value to send to the database.
If Page.User.IsInRole("MICMS") Then
    cmdCheckUser.Parameters.Add("@C_ID", 0)
    cmdCheckUser.Parameters.Add("@C_ID", Session("C_ID"))
End If

If  the user is in the role, the error is triggered saying that @C_ID
is expected by the stored procedure. If i then change the value from 0
to 10, the stored procedure works fine.Is there any reason that the stored procedure is failing when the value 0 is used and not when any other value is used?Thanking you in advance.

View Replies View Related

How To Pass Parameter B/n Stored Procedure?

How can I pass a parameter to a stored Procedure from another stored procedure in SQL 2005?
Thnak you,

View Replies View Related

Pass A Parameter To A Stored Procedure In Asp:SqlDataSource

Either method is in the “ASPX� file
This is a DataSource for a “DetailsViewâ€? which has on top of “DeleteCommandâ€? an “InsertCommandâ€? a “SelectCommandâ€? and an “UpdateCommandâ€?. It is related to a GridView and the “@DonationRecIDâ€? comes from this GridView. 
Method 1. Using an SQL Query – this works fine  <asp:SqlDataSource ID="donationDataSource" runat="server"             ConnectionString="<%$ ConnectionStrings:FUND %>"  DeleteCommand="DELETE FROM [Donations] WHERE [DonationRecID] =     @DonationRecID"> 
Method 2. – using a stored procedure – this bombs because I have no clue as to how to pass “@DonationRecIDâ€? to the stored procedure "Donations_Delete".   <asp:SqlDataSource ID="donationDataSource" runat="server"             ConnectionString="<%$ ConnectionStrings:FUND %>"    DeleteCommand="Donations_Delete"     DeleteCommandType="StoredProcedure"> How do I pass “@DonationRecIDâ€? to the "Donations_Delete" stored procedure? 
Does anyone have an example of how I can do this in the “ASPX.CS� file instead.

View Replies View Related

Pass Sort Parameter In Stored Procedure

i searched a lot to find how to pass an orderBy parameter finally i used a case block in my code and it works now how can i add a second parameter for ascending and descending order(@sortAscOrDesc)
when i use it after the end of case statement i get error
here is my sp:CREATE PROCEDURE [userPhotos]
@userID int,@orderBy varchar(100)
ASSELECT ID,UserID,Photo,ALbumID,Title,views,date_added from userAlbumPic where userID=@userID and albumID=0 order by
case @orderBy
when 'date_added' then date_added
when 'views' then [views]
else date_added

View Replies View Related

Using A Function To Pass A Parameter To A Stored Procedure

In the snippet below,  ExecuteSqlString is a stored procedure that accepts one parameter.  SelectChangeDropdownRowsource is a function in my code behind page that generates the string I want to pass.  I can't seem to find the correct syntax to get it to work.  The way it is show below, the error comes back about incorrect syntax near ')' .  Is this doable? 
<asp:SqlDataSource ID="ChangeInfo" runat="server" ConnectionString="<%$ ConnectionStrings:xxx %>"
DataSourceMode="DataReader" ProviderName="<%$ ConnectionStrings:xxx %>"
<asp:parameter name="sqlString" Type=String DefaultValue=SelectChangeDropdownRowsource()/>

View Replies View Related

How To Pass Xml File Name As Parameter To Stored Procedure

hi,i have created a stored procedure to read xml dataCREATE PROCEDURE InsertXML(@xml varchar(1000)) AS DECLARE @XmlHandle intEXEC sp_xml_preparedocument @XmlHandle output,@xmlinsert into Employee(Name,ID,Sal,Address) (SELECT Name,ID,Sal,AddressFROM  OPENXML (@XmlHandle, 'emp:EmployeeDetails/emp:Employee',2)             WITH (Name varchar(30) 'Name',                       ID int 'ID',                       Sal int 'sal',        Address varchar(30) 'Address'))EXECUTE sp_xml_removedocument @XmlHandlebut it is taking only xml text as input.but i want to send the file name as to do it.

View Replies View Related

HOw To Pass The Null Value To The Parameter Of The Stored Procedure

sSQL = "spBPT_Fuel_Set_Status_Approved"
cmdDailyPrices.CommandText = sSQL
cmdDailyPrices.Parameters.Add("@user", "Philippe")
cmdDailyPrices.Parameters.Add("@verbose", "0")
cmdDailyPrices.Parameters.Add("@Day_1_add", rowBand1.Cells(DayParameters.AddFactor).Value)
cmdDailyPrices.Parameters.Add("@Day_1_multiply", rowBand1.Cells(DayParameters.MultiplyFactor).Value)
cmdDailyPrices.Parameters.Add("@Day_2_add", "NULL")
cmdDailyPrices.Parameters.Add("@Day_2_multiply", "NULL")
For @Day_2_add and @Day_2_multiply parameters I want to pass the value as NULL not string "NULL"
could you please let me know how to do this?

View Replies View Related

How To Pass Text Parameter To Stored Procedure?

I was wondering if you could help resolve a simple question - namely how to input a type text value as a parameter to a stored procedure, which expects that type of input.

Text type variables are not allowed and casting to varchar in this case will not work as the input will be far longer than 8000 characters.


View Replies View Related

Pass Null To The Parameter In The Stored Procedure

Hi there,


     I am using SQL Reporting Services to generate reports.  I am calling the stored procedure from the reporting services. The procedure has parameters which take null.  I am stuck with passing null to the parameter from the reporting services.  I shows the error and the report is not generated.  Could you please suggest the way to pass null to the stored procedure parameter from the SQL Reporting Services.


Kindly reply me with the possible solution ASAP.


Thanks in advance




View Replies View Related

How To Pass Parameter Values To Stored Procedure In Rs

this is the error  ...

An error has occured during report processing.

Query execution failed for dataset 'dataset name'
Procedure 'procedure name ' expects parameter '@StartDate', which was not supplied

View Replies View Related

How To Pass A Parameter Which Includes More Than One Value To A Stored Procedure?



I am wondering if some of you T-SQL pros encountered a situation when you have a parameter that can consist of multiple strings. For instance, I have a stored procedure called dbo.usp_CalculateHeadcount that accepts two parameters such as @Term, and @AcadLevel. 

It works great when my parameters are two single strings; 'Fall2007', 'UG'.  But let say I have one more term such as 'Fall2006' and want to pass it to a stored procedure, the problems start to appear.

So when you execute a stored procedure:

Exec dbo.usp_CalculateHeadcount 'Fall2006','Fall2007','UG'

It doesn't work because I have added an extra string and stored procedure thinks it is another parameter.  Is there a way to handle problems like the one above?


Thanks for your feedback.


View Replies View Related

Calling A Stored Procedure And Pass Parameter One By One



I need to create a batch process which calls a stored procedure.

Here's the scenario.

I have 3 tables

Theater - TheaterId, TheaterName, Revenues,locationid, stateid
State - StateId, StateName
Location - LocationId, LocationName, StateId

There is a stored procedure spoc_updateTheater that accepts the state and location id and runs a set of sql statements against the theater table. However i want this to run for all the locations in a state one by one. There are some 700 locations in 45 states. How do i pass the location and state id one by one to the stored proc. Can i call this from a commandline or run it as batch process?




View Replies View Related

How To Pass A XML Data Parameter To An SQL 2005 Stored Procedure

How to pass a XML data parameter to an SQL 2005 Stored Procedure
I hope to insert a xml data into an typed xml column in SQL 2005.
1. I can run the Code 1 correctly.
2. I hope that I can pass a XML data parameter to an SQL 2005 Stored Procedure, So create the Code 2. but I get the error below:XQuery [cw_bookmark.Bookmark.modify()]: Only non-document nodes can be inserted. Found "xs:string ?".
3. I create the Code 3, but I get the error below:XQuery [cw_bookmark.Bookmark.modify()]: ',' or ')' expected 
4. I create the Code 4, but I get the error below:XQuery: SQL type 'xml' is not supported in XQuery.
//--------------------------Code 1-------------------------------------create procedure Hellocw_InsertBookmark40@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',asupdate cw_bookmark set Bookmark.modify('declare namespace x="";insert <x:Bookmark Id="ghdce3ak-456c-4e38-ab2f-5h02d9711b67" Title="cw" Url="kk" Description="Thte" InputDate="2004-08-12" IsPrivate="false"></x:Bookmark>as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId//--------------------------Code 1-------------------------------------
//--------------------------Code 2-------------------------------------create procedure Hellocw_InsertBookmark41@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',@Insertxml varchar(80)='<x:Bookmark Id="ghdce3ak-456c-4e38-ab2f-5h02d9711b67" Title="cw" Url="kk" Description="Thte" InputDate="2004-08-12" IsPrivate="false"></x:Bookmark>'asupdate cw_bookmark set Bookmark.modify('declare namespace x="";insert sql:variable("@Insertxml")as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId//--------------------------Code 2-------------------------------------
//--------------------------Code 3-------------------------------------create procedure Hellocw_InsertBookmark41@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',@Insertxml varchar(80)='<x:Bookmark Id="ghdce3ak-456c-4e38-ab2f-5h02d9711b67" Title="cw" Url="kk" Description="Thte" InputDate="2004-08-12" IsPrivate="false"></x:Bookmark>'asupdate cw_bookmark set Bookmark.modify('declare namespace x="";insert cast(sql:variable("@Insertxml") as xml)as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId//--------------------------Code 3-------------------------------------
//--------------------------Code 4-------------------------------------create procedure Hellocw_InsertBookmark41@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',@Insertxml xmlasupdate cw_bookmark set Bookmark.modify('declare namespace x="";insert sql:variable("@Insertxml")as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId
//--------------------------Code 4-------------------------------------

View Replies View Related

Pass Parameter Values To Stored Procedure In Dataset

I have a stored procedure "spDetailsByDay" which takes parameters @StartDateTime as datetime, @Day as int, @Hour as int, @Value1 as varchar(20), @value2 as varchar(20)


My report Parameters are StartDateTime as DateTime, Day as integer, Hour as integer, Value1 as string, Value2 as string, ReportType as string

In the dataset, I typed

=IIF(Parameters!ReportType.Value="Day", "EXEC spDetailsByDay " & Parameters!StartDateTime.Value & "," & Parameters!Day.Value & "," & Parameters!Hour.Value & "," & Parameters!Value1.Value & "," & Parameters!Value2.Value", "EXEC spDetailsByMonth")


I am getting syntax errors. Can anyone help me how to pass parameters to stored procedure in dataset.



View Replies View Related

Call SSIS Package From Stored Procedure And Pass Parameter

 I am new to the SSIS.

For DTS package of sql server 2000, I can call a DTS package from stored procedure. The command is:

dtsrun /E /SMyServer /NMyDTS /Wtrue /A Parameter1:3= 'Test'

Does anyone know, how do I do the similar thing from SSIS environment.

1) How to call a SSIS package from Stored Procedure?
2) How do I pass parameter to the SSIS package?

Thanks everyone.

View Replies View Related

Pass Multivalue Parameter To A Stored Procedure For Integer Lookup

I'd like to pass a multi-value parameter to a stored proc to satisfy an integer field lookup.

example -


@InPublicationId VARCHAR(500) = NULL AS

SELECT * from Table where PublicationId IN (@InPublicationId)

where PublicationId is defined as an int

I've seen various posts on how to split up the input string parameter to use in a string-based lookup but nothing useful for an integer-based lookup.

Any tips or tricks for this?

View Replies View Related

Syntax To Pass A Parameter Of Type Nvarchar(max) To Stored Procedure

I have a stored procedure that contains a paramteter of type nvarchar(max).  What is the syntax to pass this parameter to the sp from a VB.Net application.  Specifically, I need to know what to put in the 3rd parameter below:

cmd.Parameters.Add("@Name", SqlDbType.NVarChar, , Name)

View Replies View Related

How To Pass A Null Parameter To A Stored Procedure Executed Within A SSIS Package?

I have a SSIS package that I am trying to execute a stored procedure (the stored procedure references a linked server) within a execute sql object.  The stored procedure has two parameters. I am passing the one parameter to the SSIS package upon execution.  The second parameter is an output paramter.  When I execute the SP normally through SSMS, I just pass a null value for the second parameter.  I can't figure out how to get this to work with the execute sql task.  I created a parameter in the parameter mapping tab.  Not knowing exactly how to do it, I created a new string variable type & left the value empty.  I then set it to be an output direction.  I set the datatype to be varchar & just called it param2.

When I execute the sp, I use the following syntax:  exec sproc ?,?.  I get the following errors:

OLE DB Provider "SQLNCLI" for linked server returned message "No transcation is active.".

Executing the query exec sproc ?,? failed "the operation could not be performed because OLE DB provider "SQLNCLI" for linked server was unable to begin a distributed transation.  Possible failure reasons:  problems with the query, resultset property not set correctly, parameters not set correctly.

I have the linked server setup correctly & I am able to execute the SP through SSMS.

Any help would be appreciated!  Let me know if you need more clarification.  I can also forward my SSIS package for you to look at if you would be willing.



View Replies View Related

Can't TABLE Variable Be Used In EXEC Statement In Stored Procedure

I've used a temporary table in the stored procedure
I've created it as DECLARE @Temp TABLE (id INT)
in select clause I've used this temp table through the joins..
But I've also used a varchar variable to store my criteria...
which I'm building in the stored procedure
so inorder to use it in the where clause I used
EXEC ('SELECT ....................

FROM @Temp T

WHERE ' + @Criteria )

Unfortunately this is not working.
Giving the errror

Must declare the variable '@TempT'.
I had to use Temporary table using #, which I feel is really waste of memory...
Is there a way by which I can use my Table variable in the EXEC statement.
Thanks In advance

View Replies View Related

Loop Through A Table In A Stored Procedure

Hi, how do I loop through a table in a store procedure? I need to check the all the record in a table and do some logic and then insert or update another table base on the logic?

View Replies View Related

How To Pass Table Names To A Stored Procedure

Hi all,Seems like a fundamental question to me but I dont have a definiteanswer for it, Gurus please enlighten me.I have a table 'Table1' whose structure changes dynamically based onsome configuration values from another table. This table is being usedby a program, It was initially used by this program which ran as asingle task (executing at only a specific interval) but now the programhas to be run mutiple times some coinciding with each othe - whichmeant that table structure will change as 2 programs are runningsimultaneously... and therefore I have decided to use seperate tablenames that each has a structure of its now.I use this table name 'Table1' in about 10-15 stored procedures andUDF'sto make the long story short: Since I will not know which table I willbe using in the program I want to pass the table name as an argument tothe SP and UDF's and then access this param in the'select's/updates/inserts' - but this doesn't work unless I use DynamicSQL.Is there any other way of passing table names as parameters and thenusing then in the procs?any ideas will be really helpful.adi

View Replies View Related

How To Pass Table Name As Paramater In Stored Procedure

I am using SQL2005 and would like to know if it is possible to pass a table name as an input parameter in a stored procedure??  I am struggling to get it to work.
For example:


@TableName varchar(30),

@minX float OUTPUT,






SET @minX = (SELECT min(X) as mX

FROM @TableName);


The above does not work and give the following error

Must declare the table variable "@TableName"

View Replies View Related

EXEC Stored Procedure For Every Line Of SELECT Result Table - How?

Hello,Is it possible to EXEC stored procedure from a query?I want to execute stored procedure for every line of SELECT resulttable.I guess it's possible with cursors, but maybe it's possible to make iteasier.Give an example, please.Thank you in advance.Hubert

View Replies View Related

Pass Hash (#) Table With Different Structure To Stored Procedure

Dear Techies,I making one stored procedure, which does some operation based on aninterface hash (#) table ---- name #mydata.This stored has two section of code (seperated by parameter value 0and 1)But hash table #mydata (same name) number/name of columns changes asper call 0 or 1.e.g.when call for 0, ----> Pass 2 columns as company_cd and section_cd ininterface hash (#) table ---- name #mydata.when call for 1, ----> Pass 3 columns as Section_cd, line_cd andsubline_cd in interface hash (#) table ---- name #mydata.As a result, none of the case (0 or 1) is running properly, It givesproblem.When I execute procedure for 0 by passing #mydata with two columns---> it gives problem in 1 section codeAnd When I execute procedure for 1 by passing #mydata with threecolumns ---> it gives problem in 0 section codePlease suggest !!! If anybody have faced the same problem or have anyidea about this case.(I think passing hash table with 3 column as col1,col2,col3 can servethe purpose, but this may cause rework in my case, so looking foralternate solution)Thanks in Advance,T.S.Negi

View Replies View Related

Stored Procedure That Fetch Each Row Of A Table And Update Rows In Another Table

I am working with the following two tables:


All fields are of Integer Type.

I need to write a stored procedure in sql 2000 which works as follows:

Select all the NewID and OldID from the Category Table
(SELECT NewID,OldID FROM Category)

Then for each rows fetched from last query, execute a update query in the Link table.

For Example,

Let @NID be the NewID for each rows and @OID be the OldID for each rows.
Then the query for each row should be..


Please help me with the code.


View Replies View Related

Stored Procedure - Insert Many Rows From One Table To Another

Searched around for a sample, maybe I missed one?I have an insert statement....INSERT INTO objScores  (objID, studentId, courseId)  VALUES  (   @objID, @userId, @course)  This works fine, but problem is it reads only the first value from the tableDECLARE c_oId CURSOR FOR  SELECT objID  FROM objStructure  FOR READ ONLYThe userID and course will always be the same.But, the objID, there will be many in the table objStructure such as Lesson1, Lesson2..... Lesson19I need it to read all of them, 1 - 19 or whatever the count, could be 3 and insert all of them into the table.So, the insert needs to input Lesson1, userID, course ----- Lesson2, userId, course ----- Lesson3, userID, course ---- and so on.It must read the objID from the objStructure table and just tranfer all that is there that = the course.Links?  Suggestions?Thanks all,Zath

View Replies View Related

Stored Procedure To Return All Rows In A Table

Hi there,I need to create a stored procedure almost like "Current_user()" to returnto me the total rows in a table.. Is this possible? plz helpRudi

View Replies View Related

Stored Procedure - Delete Some Rows (table)

How to I return the row count as result of deleting some rows from a table? This this script correct below?? Here's my stored procedure script..


CREATE PROCEDURE dbo.sp_CleanUp_Table_tblSoldRaw
** CREATED: 12/04/2006
** DESCRIPTION: Clean the tblSoldRaw by deleting any data that
** is over 90 days old. We need keep the database
** as small as possible so the performance won't
** suffer on the server's and the client's machine.
** NOTES: Hooked up to BookItOut Data Importer Windows Service
** -------------------------------------------------------------
** Uses: tblSoldRaw






View Replies View Related

Copyrights 2005-15, All rights reserved