Below is a script to create a table, and one to create and execute a stored procedure on the table, I am trying to build a report that passes multiple values in one parameter, but the stored procedure takes the inputs as a comma delimited list. I came across some sites that gave script for a splitlist function that will individually delimit the list but when I put that into the stored procedure it is not recognized. Does anybody see anything wrong with what I have here and if so, have a solution?
Thanks a bunch!
CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[MiddleName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NOT NULL
INSERT INTO dbo.Customer
------------------------Create stored procedure
CREATE PROCEDURE Example
-- Add the parameters for the stored procedure here
@CustomerID int = 0
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT CustomerID, LastName
WHERE CustomerID IN (select * FROM dbo.Splitlist(@CustomerID, ',')) -------Here is the problem, I cannot get the sp to recognize the function unless I delimit the query and set it as a variable
-----and append the where statement to the end. How do I do this?
-----Execute stored procedure for the table
DECLARE @return_value int
DECLARE @CustomerId int
EXEC @return_value = [dbo].[Example]
@CustomerId = 1---------Would like to be able to execute for IN ('1','2','3')