Solved

Store SQL in a table

Posted on 2011-03-25
17
280 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
  • 7
  • 6
  • 3
  • +1
17 Comments
 
LVL 40

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 500 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
 

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 40

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 40

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 40

Expert Comment

by:Sharath
ID: 35218207
Or atleast the portion of the SP which is relevant.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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 40

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 40

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now