locked
Dynamically built queries within stored procedure RRS feed

  • Question

  • I'm working on a stored procedure that I am using for building reports for use with SSRS. In this stored procedure I am getting data from two different linked servers. The linked servers we use will depend on the location code represented by @LOC. I'm trying to keep the parameters to a minimum and dynamically build my queries. For most of the stored procedure I can use the @LOC when dynamically building my queries for use with which linked server to use. However, in the beginning of my stored procedure I must first define the values for @SHOP_SO_NO and the @SHOP_SO_SUFX for use in the following openquery statements. I can't dynamically build the first query, execute it and attempt to use it in later queries because execution issues will; number one scope the variables and number two it will not execute my final execute statement.

    Please feel free to ask any needed questions that I have not provided or clarified properly. 

    Also the below query works perfectly well. We are just attempting to plan for scalabillty using the @LOC because where each location will have it's own linked server because the data is currently different at each location. (uniformity is currently something of an issue)

    USE links
    IF EXISTS (SELECT name FROM sysobjects WHERE name = 'up_ShopEmsXX_TagInfoByTagNoAndSufx' AND type = 'P')
    	DROP PROCEDURE up_ShopEmsXX_TagInfoByTagNoAndSufx
    GO
    CREATE PROCEDURE up_ShopEmsXX_TagInfoByTagNoAndSufx 
    	@LOC char(2),
    	@TAGNO int
    	--@SHOP_SO_SUFX int
    	
    AS
    BEGIN
    
    	declare @SHOP_SO_NO nvarchar(15)
    	declare @SHOP_SO_SUFX int
    	select @SHOP_SO_NO = so_no, @SHOP_SO_SUFX = so_sufx
    	from shopfloor02_mdb...so_tags
    	where so_tagno = @TAGNO
    	
    	declare @shopquery nvarchar(255)
    	set @shopquery = 'SELECT SO_Oper, SO_Lot, SO_TagNo, Emp_No, SO_Heat, SO_HeatVendor, SO_Qty FROM so_tags WHERE so_tagno = '+ cast(@TAGNO as nvarchar) + ' and so_sufx = ' + cast(@SHOP_SO_SUFX as nvarchar)
    	declare @shopopenquery nvarchar(255)
    	set @shopopenquery = 'SELECT * FROM OPENQUERY(SHOPFLOOR02_MDB,' + ''''+@shopquery+'''' + ') SHOPFLOOR, '
    
    	declare @emsquery nvarchar(255)
    	set @emsquery = 'SELECT so_no,so_sufx, item_no, Item_Desc_1, Item_Desc_2, Qty_Ord, CAST(Ord_Strt AS varchar(10)) AS Ord_Start FROM so_hdr WHERE loc = ''''' + @LOC + ''''' and so_no=''''' + '  ' + @SHOP_SO_NO + '''''' + ' and so_sufx = ' + cast(@SHOP_SO_SUFX as nvarchar)
    	declare @emsopenquery nvarchar(255)
    	set @emsopenquery = 'OPENQUERY(EMS' + @LOC + ',' + ''''+@emsquery+'''' + ') EMSHDR, '
    
    	declare @emsOperquery nvarchar(255)
    	set @emsOperquery = 'SELECT Oper_No, Dept_Mfg, WC, Oper_Desc FROM so_oper WHERE loc = '''''+ @LOC +''''' and so_no=''''' + '  ' + @SHOP_SO_NO + '''''' + ' and so_sufx = ' + cast(@SHOP_SO_SUFX as nvarchar)
    	declare @emsOperopenquery nvarchar(255)
    	set @emsOperopenquery = 'OPENQUERY(EMS' + @LOC + ',' + ''''+@emsOperquery+'''' + ') EMSOPER '
    
    
    	declare @fullquery nvarchar(2000)
    	set @fullquery = @shopopenquery + @emsopenquery + @emsOperopenquery +
    	'where EMSHDR.so_no = ''  ' + @SHOP_SO_NO + ''' and EMSHDR.so_sufx = ' + cast(@SHOP_SO_SUFX as nvarchar) + ' and EMSOPER.Oper_No = SHOPFLOOR.SO_Oper'
    	
    	exec(@fullquery)
    	--print @fullquery
    END
    
    


     


    Chris "Is there anything besides programming?"
    Monday, July 18, 2011 4:28 PM

Answers

  • I came up with a solution but I don't recall what it was. I'm marking this as answered.

    Chris "Is there anything besides programming?"

    Monday, November 5, 2012 5:14 PM

All replies

  • To clarify I need to change the first first query so that it is dynamically built and still works with the rest of the SQL code. Any ideas?
    	declare @SHOP_SO_NO nvarchar(15)
    	declare @SHOP_SO_SUFX int
    	select @SHOP_SO_NO = so_no, @SHOP_SO_SUFX = so_sufx
    	from shopfloor02_mdb...so_tags
    	where so_tagno = @TAGNO
    


    Chris "Is there anything besides programming?"
    Monday, July 18, 2011 4:30 PM
  • Also, just an FYI, I created two other stored procedures for use with dynamically building this query that return the SO_NO and SO_SUFX respectively. It all executed ok but it was scoping the variables to that single select so that later in the stored procedure when executing the openqueries the variable values weren't assigned due to scope.
    Chris "Is there anything besides programming?"
    Monday, July 18, 2011 4:36 PM
  • I gave terrible examples. It should end up something like this; "ShopFloor" + @LOC + "_mdb...so_tags" and the rest of the code should be usable with the variables that it assigns values to.
    Chris "Is there anything besides programming?"
    Monday, July 18, 2011 5:38 PM
  • I came up with a solution but I don't recall what it was. I'm marking this as answered.

    Chris "Is there anything besides programming?"

    Monday, November 5, 2012 5:14 PM