Skip to Content
author's profile photo Former Member
Former Member

SQL: Using the result of a StoredProcedure as View or run a cursor over it?

Dear SQL-experts,

is it possible to use the results of a Stored Procedure in a same manner as a SELECT-result?

I would like to run with a cursor over the SP-result.

The background:

I want to write my own SP. One parameter of this SP is a comma seperated list of pallets which must be built into the first query inside the SP (as the IN ('x','y','z') -list).

The problem:

The only way I know to build a dynamical query is the use of sp_executesql and the query as a VARCHAR

CREATE PROC MyExampleSP (@DocNum INT, @PalletList AS NVARCHAR(max)) as
BEGIN
	DECLARE @code nvarchar(8)

	DECLARE @query AS NVARCHAR(max)

	SET @query=
	N'SELECT 
		T1.Code 
	FROM 
		[@TST_XXX] T0
	WHERE 
		T0.U_InvDoNu='+CAST(@DocNum AS VARCHAR(20))+' AND 
		T0.U_PalletNo IN ('+@PalletList+')'

	exec sp_executesql @query
	-- NOW:
	-- How to work with the result of sp_executesql inside this procedure?
	-- A cursor over the result would be needed. Is this possible?

END

The new procedure is called like this (the length of the list is varying and only known at runtime):

DECLARE @PalletList as NVARCHAR(max)
SET @PalletList=N'''S100014555'', ''S100014556'', ''S100014574'''

exec MyExampleSP 500157, @PalletList

Or is there another way to store lists for IN ('x','y','z') in a special SQL-var-type which I don't know yet?

Or a completely different idea?

Thanks,

Roland

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Oct 29, 2008 at 08:12 PM

    Hi Roland,

    There's a few ways you could tackle this, but I'd go for the following:

    1) Create a SQL function that splits the comma-delimited string of pallets, queries your UDT and then returns a table of the results

    create function fn_MYNAMESPACE_SplitCSV  (@DocNum int, @Input varchar(1000))
    returns @Results table (Code nvarchar(50))
    as
    
    begin
    
    declare @i int
    declare @x int
    declare @Pallet nvarchar(50)
    
    	set @i = 1
    	while @i <= len(@Input)
    	begin
    		select	@x = charindex(',', @Input, @i)
    		if @x = 0
    			select	@x = len(@Input) + 1
    
    		set @Pallet = substring(@Input, @i, @x - @i)
    
    		insert into @Results 
    			select 
    				T0.Code 
    			from 
    				[@TST_XXX] T0
    			where 
    				T0.U_InvDoNu= @DocNum and 
    				T0.U_PalletNo = @Pallet
    
    		set	@i = @x + 1
    
    	end
    
    	return
    
    end
    

    Note: To use the above function, your string of pallets should be

    DECLARE @PalletList as NVARCHAR(max)
    SET @PalletList=N'S100014555,S100014556,S100014574'
    

    so that the comma is the only delimiting character between the pallet codes.

    2) In your proc, create a temporary table and call the function to populate the table. I've never been a fan of SQL cursors so I prefer to use a conditional loop to iterate through the results:

    CREATE PROC sp_MYNAMESPACE_MyExampleSP (@DocNum INT, @PalletList AS NVARCHAR(max)) as
    BEGIN
    	
    	declare @Total int
    	declare @Step int
    	declare @Code nvarchar(8)
    	create table #Codes (idx int identity(1, 1), Code nvarchar(8))
    
    	set nocount on
    
    	insert into #Codes
    		select * from dbo.fn_MYNAMESPACE_SplitCSV(@DocNum, @PalletList)
    
    	set @Total = @@RowCount
    	set @Step = 1
    
    	set nocount off
    
    	while @Step <= @Total
    		begin
    			select @Code = Code from #Codes where idx = @Step
    
    			-- Insert your code here
    			-- I'm just going to return each code for this example
    			select @Code
    
    			set @Step = @Step + 1
    		end
    
    	drop table #Codes
     
    END
    

    Kind Regards,

    Owen

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hello Owen!

      Thank you very much!! 😊)

      Here's what I made of it:

      CREATE FUNCTION fn_NAMESPACE_SplitCSV  (@Input VARCHAR(1000))
      RETURNS @Results TABLE (SplitValue NVARCHAR(50))
      AS
       
      BEGIN
       
      	DECLARE @i INT
      	DECLARE @x INT
      	DECLARE @SplitValue NVARCHAR(50)
       
      	SET @i = 1
      	
      	WHILE @i <= LEN(@Input)
      	BEGIN
      		SET @x = CHARINDEX(',', @Input, @i)
      		IF @x = 0
      			SELECT @x = LEN(@Input) + 1
       
      		SET @SplitValue = RTRIM(LTRIM(SUBSTRING(@Input, @i, @x - @i)))
       
      		INSERT INTO @Results (SplitValue)VALUES(@SplitValue)
       
      		SET	@i = @x + 1
      	END
      	RETURN
      END
      

      And here how it can be used inside the SP:

      DECLARE @DocNum INT
      DECLARE @PalletList as NVARCHAR(max)
      SET @DocNum=500157
      SET @PalletList=N'S100014555, S100014556, S100014557'
      
      SELECT 
      	T1.Code 
      FROM 
      	[@TST_XXX] T0
      WHERE 
      	T0.U_InvDoNu=@DocNum AND 
      	T0.U_PalletNo IN (SELECT SplitValue FROM dbo.fn_NAMESPACE_SplitCSV(@PalletList))
      

      You solved my main problem.

      And the alternative for a cursor and many other things in your example have given me many new skills. 😊

      I'm in the mood for giving you 100 points but the forum system restricts me to poor 10 points... 😉

      Thanks again and regards,

      Roland

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.