Return Statement Executed from Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Return Statement Executed from Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-28 : 08:15:19

Chantay writes "I've got an ASP.NET page that uses multiple drop down lists to determine selection criteria to the database (SQL Server 2000) -- development is on Windows XP Pro. Most of the values are set, but the first option in the drop down list sends a wildcard to a stored procedure via parameters. The problem is I think the wildcard values aren't provided correctly, or the interface to SQL server is dropping them (fear of sql injection?).

I'd like to be able to return the actual statement executed from the stored procedure. For example, if my stored procedure is:


CREATE PROC dbo.DoSomething
  @stuff as varchar,
AS
  SELECT * 
  FROM myTable
  WHERE col1 = @stuff
GO

the return value would be


SELECT *
FROM mytable
WHERE col1 = 'hello'

Is this possible?"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-04-28 : 08:24:32

It sounds like if you are passing "*" as your wild card. SQL wont like that.

CREATE PROC dbo.DoSomething
(
@stuff as varchar(25)
)
AS

IF @stuff = '*'
BEGIN
SELECT @stuff = '%%'
END

SELECT
<Column list> -- because SELECT * is nasty
FROM
myTable
WHERE
col1 LIKE @stuff

Of course, using the '%%' kills use of an Index. There may be a better way.

EDIT added a size for Varchar declaration
EDIT #2 Used like so query will actually work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources