p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   Passing a parameter value to Stored Procedure (http://p2p.wrox.com/showthread.php?t=19272)

mcinar October 1st, 2004 11:45 AM

Passing a parameter value to Stored Procedure
Hi All,

I am using VB 6.0 with SQL Server 2000
and I am trying to pass a string value to a stored prcodeure to use
in "WHERE" clause.


In VB:
strWhere = "StateID = 1"

By using the "Command Object", I pass "strWhere" parameter

In Stored Procedure:
create up_parmSel_state
    @strWhere as varchar(255)
 select *
   from State_T
   where @strWhere

* That's what I am trying to accomplish, but it does not work.
* I know that there is an other way by using some type of SQL Function to make it work in the Stored Procedure,
but I don't remember it now.

Thank you in advance.

jemacc October 1st, 2004 10:44 PM


CREATE PROCEDURE up_parmSel_state
   (@strWhere as varchar(255))AS
FROM state_t
WHERE <column> = @strWhere


EXEC up_parmSel_state @strWhere= 1

mcinar October 1st, 2004 11:03 PM

Thank you for reply.
What I need to try is to also pass "actual column id" with the value. In this case, "State_ID" is my column name and the value is "1".
The reason that I need this type of logic is to be able to pass multiple columns with their values based on the filtering conditions.

For Example;

In Visual Basic:

I have "State" and "Industry" options for user to choose. Let's say a user picks "State" but not "Industry". In this case my string variable
"strWhere" will be "State_ID = 10" Let's say #10
corresponds to "NY" from the pick list.
The advantage of having this type of logic prevents the "if" logic in the "Stored Procedure".

Thank you.

jemacc October 2nd, 2004 12:48 AM

you probably have to use something like sp_executesql

post your table columns

I can get you this far:

create proc up_parmSel_state
DECLARE @IntVariable INT
DECLARE @ParmDefinition VARCHAR(50)
SET @SQLString = N'SELECT * FROM state_t WHERE stateID = @stateID'
SET @ParmDefinition = N'@stateID int'
/* Execute the string. */
SET @IntVariable = 1

EXECUTE sp_executesql @SQLString, @ParmDefinition, @stateID= @IntVariable

joefawcett October 2nd, 2004 06:26 AM

One small thing, you're better declaring the strings as NVARCHAR originally as that's what sp_executesql expects rather than convert on route.



mcinar October 2nd, 2004 07:14 PM

Thank you jemacc and joefawcett.

I will ask a similiar question;

Let's say,

create procedure up_parmSel_State
   @TableName as varchar(255)
  select *
     from @TableName
execute up_parmSel_State "State_T"

I know that this won't work.
I need to use different type of sql statement to make it work. But, I don't remember it.

Thanks in advance.

jemacc October 3rd, 2004 12:19 AM

Caution using a variable for your table name causes security risk

use the below adding your where clause

create proc up_parmSel_state
declare @SQL varchar(255), @tblname sysname

SELECT @tblname = 'state_t'

SELECT @SQL = 'SELECT * FROM ' + @tblname
SELECT @SQL = @SQL + ' WHERE (stateID) = 12345'

mcinar October 3rd, 2004 08:41 AM

This is a very good example and it will work for me.

Thank you, Jemacc.

jemacc October 3rd, 2004 11:01 AM

Here is the alternative to your original question

create proc up_parmSel_state
declare @SQL varchar(255),@state_ID varchar(30) @tblname sysname

SELECT @tblname = 'state_t'

SELECT @SQL = 'SELECT * FROM ' + @tblname
SELECT @SQL = @SQL + ' WHERE (state_ID) = ' + @state_ID'

mcinar October 3rd, 2004 09:42 PM

Thank you jemacc.

All times are GMT -4. The time now is 03:05 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.