• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

Store SQL in a table

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
Larry Brister
Asked:
Larry Brister
  • 7
  • 6
  • 3
  • +1
1 Solution
 
SharathData EngineerCommented:
Can you eloborate your question? Do you mean more sqltext queries are returned from valueUCC_CollateralDescription?
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
bitrefCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Larry Bristersr. DeveloperAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
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
 
SharathData EngineerCommented:
Can you post your enitre SP which will be invoked when the user select the data in the drop down.
0
 
SharathData EngineerCommented:
Or atleast the portion of the SP which is relevant.
0
 
Larry Bristersr. DeveloperAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
Sharath_123

Paramaters
 @SeqNumber int
,@searchName nVarChar(50)
,@CTLienStatus nVarChar(150)
,@alphaSearch nVarChar(10)
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
I'm not looping over storeproc names, I'm looping over params.

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
lrbrister:

http://#35216738

Is there problem with this?  You haven't responded to my comment.
0
 
SharathData EngineerCommented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 7
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now