How to exec a stored procedure for a list of constant values?
declare variable in sql stored procedure
execute stored procedure sql server with input parameters
how to set default parameter value in stored procedure in sql server
how to assign a value to a variable in sql server stored procedure
which keyword can you use to pass parameter from a stored procedure back to the calling program?
stored procedure with input and output parameters in sql
how to pass parameter in sql query
I can exec a stored procedure for a single value easily:
EXEC FooStored @ID = 269
But how can I execute the same stored for many values, which I have in a list (I have a comma separated list of about 1000 constant values, which came from outside of SQL).
I was thinking something along the lines of:
EXEC FooStored @ID IN (269,270,274,280,282,292,300,320,324) -- doesn't work
Edit: Since I don't have permissions to alter this procedure or make new stored procedures to this DB, I would prefer to solve this on the querying level, rather than ask someone to make the needed changes for me.
Below is an example using a table-valued parameter.
I see from your comment on the string-splitting answer that you don't want to modify the existing stored procedure interface. Consider creating a new stored procedure for the list interface or overloading the existing interface with the optional list parameter to avoid a breaking change.
CREATE TYPE dbo.IntegerList AS TABLE( IntegerValue int NOT NULL PRIMARY KEY ) GO CREATE PROC dbo.FooStored @IntegerList dbo.IntegerList READONLY AS SELECT * FROM dbo.YourTable WHERE ID IN( SELECT IntegerValue FROM @IntegerList ); GO DECLARE @IntegerList dbo.IntegerList; INSERT INTO @IntegerList VALUES(269),(270),(274),(280),(282),(292),(300),(320),(324); EXEC dbo.FooStored @IntegerList = @IntegerList; GO
Specify Parameters, By specifying procedure parameters, calling programs are able to pass They illustrate how to pass parameters as constants and variables and to the parameter names, such as: EXECUTE my_proc @second = 2, @first Say we have a stored procedure that queries the Stack Overflow Users table to find people in a given location. Here’s what the table looks like: And here’s what my starting stored procedure looks like: [crayon-5efd754218495776142793/] It works: And the actual execution plan isn’t bad, although it manages to overestimate the number of people who
You should use Table types In your stored procedure use a user defined table type as type of your value. it could be some thing like this:
CREATE PROCEDURE FooStored ( @IDs ListOfID READONLY ) AS . . .
ListOfID is a user defined table type as you can see it below:
CREATE TYPE ListOfID AS TABLE ( [ID] int NULL )
to use your FooStored stored procedure you must at first declare a temp table and insert your data into that temp Table and pass your it to your stored procedure. it could be some thing like this:
DECLARE @list ListOfID INSERT INTO @list VALUES(1) INSERT INTO @list VALUES(2) EXEC FooStored @list
Variables in SQL Server Stored Procedures, To store the value returned by a stored procedure or a function and execute the following code block to get a list of products whose model year is 2018:. The EXEC command is used to execute a stored procedure, or a SQL string passed to it. You can also use full command EXECUTE which is the same as EXEC. Syntax of EXEC command in SQL Server. Following is the basic syntax of EXEC command in SQL Server.
You need to take parameter as comma separated string into stored procedure, and then check inside the stored procedure "if '@ID' is in the list."
If you are using SQL Server 2016 and above you can use
If you are using lower version of SQL Server then you can use this function to split the string:
CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) ) RETURNS @returnList TABLE ([Name] [nvarchar] (500)) AS BEGIN DECLARE @name NVARCHAR(255) DECLARE @pos INT WHILE CHARINDEX(',', @stringToSplit) > 0 BEGIN SELECT @pos = CHARINDEX(',', @stringToSplit) SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1) INSERT INTO @returnList SELECT @name SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos) END INSERT INTO @returnList SELECT @stringToSplit RETURN END
To split the string inside stored procedure:
IF (@ID IN (SELECT * FROM dbo.splitstring(@CommaSeparatedString))
NB! Be careful with white spaces when using above
splitstring function. For values like '269, 270, 274' -> space after commas:
IF (@ID IN (SELECT RTRIM(LTRIM([Name])) FROM dbo.splitstring(@CommaSeparatedString))
Stored Procedure Execution with Parameters, Variables, and , Parameters are values that are being passed into a stored procedure. Since these are part of the call to execute the procedure; these are “known” and the average number of rows for most names is quite high (where an To execute a stored procedure In Object Explorer, connect to an instance of the SQL Server Database Engine, expand that instance, and then expand Databases. Expand the database that you want, expand Programmability, and then expand Stored Procedures. Right-click the user-defined stored procedure that you want and click Execute Stored Procedure.
As @ikram and @DanGuzman said, the only way to do this, without editing the existing procedure or making a new one, is to just exec the stored N times. The query can be built quite fast with for example Vim.
Starting from the file (a lot more numbers in reality):
And running the commands:
qqf,s<Enter><Esc>q 1000@q <C-V>ggIEXEC FooStored @ID =
EXEC FooStored @ID = 269 EXEC FooStored @ID = 270 EXEC FooStored @ID = 274 EXEC FooStored @ID = 280 EXEC FooStored @ID = 282 EXEC FooStored @ID = 292 EXEC FooStored @ID = 300 EXEC FooStored @ID = 320 EXEC FooStored @ID = 324
SQL Server Stored Procedure Input Parameter, Output Parameter , A value for an output parameter can be returned to a calling script. Return codes from a return statement within a stored procedure can achieve two goals. Parameter names must begin with an @ symbol. input parameters just like any constants that you would use to assign values to input parameters. Execute The Stored Procedure. Before executing the stored procedure, declare a variable for the UDT and insert some values. Then pass the variable to the stored procedure while executing it.
Where Should I Keep Stored Procedure Names in my Code , Short answer: you are fully correct, using constants is not the only possible solution for cases like this, and yours is probably better. Longer Return Values in SQL Stored Procedure Example 1. In this example, we will show you the default return value returned by the SQL Server. To demonstrate the same, let me execute the previously created Stored Procedure. Please refer to Input Parameters in a Stored procedure article to know the definition behind this stored procedure.
Comprehensive Informatics Practices Xii, Execute method The following table lists the names and descriptions of the constant Constant values for the adStatus Parameter of ADODB EVENT- HANDLING of the Command object is to execute parameterized stored procedures, Say we have a stored procedure that queries the Stack Overflow Users table to find people in a given location. Here's what the table looks like: Here's what the table looks like: And here's what my starting stored procedure looks like:
Microsoft Access 2010 In Depth, Presetting all property values of the Command object, rather than supplying argument values to the Execute method, makes your VBA code easier for others to comprehend. When used in ADO code, names of stored procedures and views having spaces must be For lngOptions constant values, refer to Table 32.7. Stored Procedure With Multiple Parameters. Setting up multiple parameters is very easy. Just list each parameter and the data type separated by a comma as shown below. The following SQL statement creates a stored procedure that selects Customers from a particular City with a particular PostalCode from the "Customers" table:
- A table-valued parameter would be best. Another option is passing the delimited list as a single string and then splitting the values.
- @DanGuzman I'm not familiar with either of those techniques. Would you mind expanding your comment into an answer?
- What is your SQL version?
- If you don't want to change the existing procedure, then the best option would be to copy the current stored procedure and change the copy to work with a table valued parameter. I know that it kinda violates DRY but that's usually less important in SQL then in programming.
- @Zhorov SQL Server 2016
- Added an edit to my question, which clears up my needs.
- @ruohola, there is no T-SQL solution if you can't change the proc or create a new one.
- Thanks, but I edited my question to state that I would prefer not to edit the existing procedure.
- So the only way to do that is to execute your stored procedure as many times as the length of your list
- Yeah, that seems to be the case, my submitted answer also reflects that.
- I would prefer overloading stored procedure by adding optional parameter As @Dan Guzman said. This way other users will not affect from your change
- Added an edit to my question, which clears up my reasoning.
- If you have no right to alter or create a new sp, then the only way is to calling sp N times I think
- That might be the case... To bad if it really is.