how to: select * from table where combobox = textbox 3

[puitar]

Greets,

I'm new to stored procedures.

I want to create a dynamic stored procedure that will accept a different parameter based on a field selected from a combo box on an asp page. If this doesn't make sense please tell me. The procedure must be able to construct a different where statement based on the selected value of a combo box filled with field names. A text box will accept and send a query string.

Any help is good.

p

 

[TomasDill]

Hi!

In the stored procedure you can do something like following:

declare @strSQL varchar(8000), @strSQL1 varchar(8000),
@strSQL2 varchar(8000), @strSQL3 varchar(8000)


select @strSQL = 'SELECT ' + @ListOfFields
select @strSQL1 = 'FROM ' + @LitsOfablesAndJoins
select @strSQL2 = 'WHERE ' + @CustomWhereConditions
select @strSQL3 = 'Group By... Order By... ' + @CustomOrder -- or Grouping - whatever is additional needed

EXEC (@strSQL + @strSQL1 + @strSQL2 + @strSQL3)

This way you build SELECT statement in the string and execute it on SQl Server. Note that you cannot use variables inside of such SQL statement because EXEC function runs SELECT statement in another session and process.

Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[xcata]

Hi,
With the help of sp_executesql you may also variables inside a dynamic sql statement.

Example from BOL:

DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

/* Build the SQL string once.*/
SET @SQLString =
N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint'
/* Execute the string with the first parameter value. */
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@level = @IntVariable
/* Execute the same string with the second parameter value. */
SET @IntVariable = 32
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@level = @IntVariable


Hope this helps.

 

[puitar]

Thanks guys,

Do you know of any good sites that have tutorials starting from a beginners level to advanced?

p

 

[tlbroadbent]

Check faq183-694, "Where can I find training/tutorials for SQL and T-SQL?" for an extensive list of online resources for learning SQL and T-SQL. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous