Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Store SQL in a table

Posted on 2011-03-25
17
Medium Priority
?
298 Views
Last Modified: 2012-08-13
I have a dropdown list on my page that when selected I want to execute one of possibly 500 functions or stored procedures based on the one they select.

How would I place the code in the varchar field is such a way that I can pass in the variables?
For instance..example one below works because I have the text hard coded in the table

But how can I execute example 1 when placed in the table AND be able to pass in the 3 (or more) paramaters dependong on the row I'm trying to execute?
--EXAMPLE 1
Declare @sqlText varchar(1000)
Set @sqlText = (Select sqlText from valueUCC_CollateralDescription where id = 1)
--Select @sqlText returns this value:  'sp_portal_UCCListGet 1,'','''
exec(@sqlText)

Open in new window

0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 3
  • +1
17 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35216686
Can you eloborate your question? Do you mean more sqltext queries are returned from valueUCC_CollateralDescription?
0
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 2000 total points
ID: 35216738
you can use replaces:

setup your original sql as this:

sp_portal_UCCListGet <param1>, <param2>, <param3>

would also make another column called Expected Paramaters (in this example would be 3)

in your main execute stored procedure, pass in a delimited string of params (say ';')

use fn_txt_split to get a table of parameters eg:

select *  into #params from fn_txt_split(@ParamList, ';')

loop over #params using the code below (typed in here, didn't verify syntax)

declare @i int
set @i = 0
declare @param varchar(256)

while (select count(*) from #params >= 1)
begin
   set @i = @i + 1
   set @param = (select top 1 item from #params)
   @sql = replace(@sql, 'param' + cast(@i as varchar(3), @param)
   set @@rowcount = 1

   --delete param
   delete from #params
   set @@rowcount = 0


end



/****** Object:  UserDefinedFunction [dbo].[fn_Txt_Split]    Script Date: 03/25/2011 12:20:17 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Txt_Split]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_Txt_Split]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_Txt_Split]    Script Date: 03/25/2011 12:20:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


Create Function [dbo].[fn_Txt_Split]( 
    @sInputList varchar(8000) -- List of delimited items 
  , @Delimiter char(1) = ',' -- delimiter that separates items 
) 
RETURNS @list table (Item varchar(8000)) 
as begin 
DECLARE @Item Varchar(8000) 
  
  

WHILE CHARINDEX(@Delimiter,@sInputList,0) <> 0 
BEGIN 
SELECT 
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0 
)-1))), 
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList)))) 
  
IF LEN(@Item) > 0 
INSERT INTO @List SELECT @Item 
  
END 

  
IF LEN(@sInputList) > 0 
INSERT INTO @List SELECT @sInputList -- Put the last item in 
  
return 
END 

GO

Open in new window

0
 
LVL 5

Expert Comment

by:bitref
ID: 35218078
You may create a table with two columns ScriptID and ScriptText. Whenever the user selects a function from the DropDownList, retrieve the function ID and use it to get the equivalent Script from the table to execute.
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

Author Comment

by:lrbrister
ID: 35218095
Sharath_123:
The name of the stored procedure is stored in a field without the paramaters

I want to execute the stored procedure that is selected and tag on the paramaters
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35218122
So the parameters are always blank? In any case, you have to loop over for all the sp names returned. Check http:#35216738. ged325 is trying to loop over the returned SP names.
0
 

Author Comment

by:lrbrister
ID: 35218157
Sharath_123:
Maybe I'm just not stating this correctly

This table will be used to populate a drop down list with
ddl.value = id
ddl.text = txtDescription

When they select a single item from the dropdown list, I grab the id...and go get the sqlText (which is the stored procedure)

I know the variables that the different items need...
I just need to execute the stored procedure with the paramaters tagged on.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35218201
Can you post your enitre SP which will be invoked when the user select the data in the drop down.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35218207
Or atleast the portion of the SP which is relevant.
0
 

Author Comment

by:lrbrister
ID: 35218377
Sharath_123:

If I do this
Select * from valueUCC_CollateralDescription where id = @id (@id = 1 for testing)

This is what is returned
 ScreenPrint
If I do it that way it will be done to get the actual text I want
Declare @sqlText varchar(1000)  
Set @sqlText = (Select sqlText from valueUCC_CollateralDescription where id = 1)  
Select @sqlText

returns sp_portal_UCCListGet

I want to use the sqlText column (@sqlText) and marry that with 4 paramaters

@p1,@p2,@p3, @p4

And then execute the following
exec @sqlText  @p1,@p2,@p3, @p4

An example of how that would be rendered is
exec sp_portal_UCCListGet 1,'','',''
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35218397
ok, do you have these variables @p1,@p2,@p3, @p4 defined in your SP and catching the values from drop down into these variables? What are the data types of these variables?
0
 

Author Comment

by:lrbrister
ID: 35218549
Sharath_123

Paramaters
 @SeqNumber int
,@searchName nVarChar(50)
,@CTLienStatus nVarChar(150)
,@alphaSearch nVarChar(10)
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 35218730
I'm not looping over storeproc names, I'm looping over params.

0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 35243824
lrbrister:

http://#35216738

Is there problem with this?  You haven't responded to my comment.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35243937
Is the sqlText column in valueUCC_CollateralDescription table contains only the SP name or SP name with parameters as blank values enclosed in quotes as you mentioned in the original post?
0
 

Author Comment

by:lrbrister
ID: 35244318
Hey folks...I had emergency knee surgery and will look at this in detail as soon as I'm not so loopy with the meds.

ged325: looks like the ticket...but I need to test.
0
 

Author Comment

by:lrbrister
ID: 35283277
Ok folsk...both had good ideas and gave me several ways to go.

My attached code example is what I wound up going with (basic version)

So...seems that ged325: gave me the best start.  Will award points there later today if no objection
use portal

Declare @sqlText varchar(200) -- function/sp name
Declare @params varchar(200)  -- paramaters string
Declare @id int
Set @id = 2					  -- comes in from vb

Select @sqlText = sqlText, @params = params  -- set sql and paramaters 
from	dbo.valueUCC_CollateralDescription
where	id= @id

Declare @sql varchar(8000)   
Set @sql = 'Select dbo.' + @sqlText + '(' + @params + ')'
--print @sql
exec(@sql)

Open in new window

0
 

Author Closing Comment

by:lrbrister
ID: 35298392
Thanks
0

Featured Post

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question