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
 General SQL Server Forums
 New to SQL Server Programming
 Variables in Where Clause

Author  Topic 

SyDiko
Starting Member

22 Posts

Posted - 2013-08-20 : 14:54:49
Hi Guys,

I need some help with my SQL logic, and I've been working (and researching) this all day with zero success... So I turn to the gurus! :)

My goal is to try an pass a variable from an ASP page to a stored procedure, which is utilizing the variable as criteria for a column_name in the where clause.

So for example (a simplified version of my query):

@strDept nvarchar(10), @strUser nvarchar(30)
-- the asp page will pass f19 to @strDept
-- the asp page will pass the logged in username to @strUser

select x, y, z from table1 where @strDept in (@strUser)

Now my question is: Is this at all possible? The stored procedure does execute, but it returns no values. Is that because the where variable has no data at compile time? If not, I can't think of any reason why this is not working.

Can anyone recommend a better way to do this? (I have tried a case statement, before the select to set the variable too and that returned the same result.)

(Thank you for taking the time to read my post by the way.)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-20 : 15:55:56
You can do a lot of "interesting" things with Dynamic SQL. I'm not sure I follow the logic, but here is a very unsafe query:
EXEC ('select x, y, z from table1 where ' + @strDept + ' in (' + CHAR(39) + @strUser + CHAR(39) + ')'
My question is, why would you pass in the column name? Wouldn't you already know what it is? Perhaps, there is no need for dynamic sql at all and you can just pass the User to a stored procedure?

Here is a link that has lots of info on dynamic sql:
http://www.sommarskog.se/dynamic_sql.html

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-20 : 16:26:41
Perhaps a warning against SQL injection is is proper?
DECLARE	@SQL NVARCHAR(MAX);

SET @SQL = 'SELECT x, y, z FROM dbo.Table1 WHERE ' + QUOTENAME(@strDept) + ' IN (' + QUOETNAME(@strUser, '''') + ');';
EXEC (@SQL);



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SyDiko
Starting Member

22 Posts

Posted - 2013-08-20 : 16:28:29
quote:
Originally posted by Lamprey

You can do a lot of "interesting" things with Dynamic SQL. I'm not sure I follow the logic, but here is a very unsafe query:
EXEC ('select x, y, z from table1 where ' + @strDept + ' in (' + CHAR(39) + @strUser + CHAR(39) + ')'




Oh wow, didn't know you could do that - I will try and apply to my where clause.

quote:
Originally posted by Lamprey
My question is, why would you pass in the column name? Wouldn't you already know what it is? Perhaps, there is no need for dynamic sql at all and you can just pass the User to a stored procedure?



Well, that's the thing - technically I'm writing the stored procedure as a query and the results are going to be displayed on 2 types of pages. The first page is a manager view, and this page will display all the data listed in the criteria. The second report is an individual view per department, which is modeled by my example above. The problem is that each department has their own field name where the user's full name is stored, and in order to write less code, I figured a dynamic field name passed into the sp based on the asp variable would solve that. So for example, the sales field would be field1, and the processor is field2. Instead of writing 2 if statements to handle that, why not use dynamic sql to pass that info from the page to the sp?

I'm somewhat familiar with sql injection too, and I don't think it applies here, because the page is passing 2 static parameters and the user doesn't know anything about it. The page passes a 0 or 1 for the page view (individual or manager) and also the fieldid for the person's name) so they can see their own data. They are basically dashboards, (if that makes sense.)




(and thank you for the resource, I will take a look.)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-20 : 18:39:10
Never underestimate the stupidity, or geniality, of users.
If there is a crack in your system, someone will find it soon or enough and exploit it.




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SyDiko
Starting Member

22 Posts

Posted - 2013-08-21 : 09:53:56
Erm, still no luck with the above code and/or in the dynamic article.

Any other suggestions?

quote:
Originally posted by SwePeso

Never underestimate the stupidity, or geniality, of users.
If there is a crack in your system, someone will find it soon or enough and exploit it.




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA



True that.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-21 : 10:03:55
quote:
Originally posted by SyDiko

Erm, still no luck with the above code and/or in the dynamic article.

Any other suggestions?

quote:
Originally posted by SwePeso

Never underestimate the stupidity, or geniality, of users.
If there is a crack in your system, someone will find it soon or enough and exploit it.




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA



True that.

Swepeso's code works correctly in my tests - see below. Can you post your code that does not work, or follow this example and see what you are doing differently?

CREATE TABLE #Table1 (x INT, y INT, z INT, f19 VARCHAR(32), f20 VARCHAR(32));
INSERT INTO #Table1 VALUES (1,2,3,'Smith',NULL);
INSERT INTO #Table1 VALUES (7,8,9,NULL,'Jones');
GO
-- Look in column f19.
DECLARE @SQL NVARCHAR(MAX);
DECLARE @strDept VARCHAR(32) = 'f19';
DECLARE @strUser VARCHAR(32) = 'Smith';

SET @SQL = 'SELECT x, y, z FROM #Table1 WHERE ' + QUOTENAME(@strDept) + ' IN (' + QUOTENAME(@strUser, '''') + ');';
EXEC (@SQL);
GO
-- Look in column f20.
DECLARE @SQL NVARCHAR(MAX);
DECLARE @strDept VARCHAR(32) = 'f20';
DECLARE @strUser VARCHAR(32) = 'Jones';

SET @SQL = 'SELECT x, y, z FROM #Table1 WHERE ' + QUOTENAME(@strDept) + ' IN (' + QUOTENAME(@strUser, '''') + ');';
EXEC (@SQL);
GO

DROP TABLE #Table1;
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-21 : 10:41:08
I have to ask: is ther any chance you can fix your data structure? It seems very odd to have a column for each department. My guess is that it should be normalized so that you have a column for department(ID) and column for the Users Name. That would avoid the situation you are trying to find a solution for.
Go to Top of Page

SyDiko
Starting Member

22 Posts

Posted - 2013-08-21 : 14:02:04
Lamprey, I'm unable to edit the database - it was created by another company that works with their own software system. I'm merely querying the data via a custom view.

quote:
Originally posted by James K
Swepeso's code works correctly in my tests - see below. Can you post your code that does not work, or follow this example and see what you are doing differently?





I'll post the exact stored procedure..

quote:

[code]
ALTER PROCEDURE [dbo].[poli_platform_appraisalOrdered] @strDept nvarchar(50), @strUser nvarchar(50), @strDash nvarchar(1)
AS
set nocount on

-- Processing View
IF @strDash = '0'
Begin
select
f1 as 'Filename',
f101 as 'Last Name',
f18 as 'Credit Processor',

from all_fields where

foldername in ('active')
and f6315 not in ('1/1/2000')
and datediff(d,f6315,getdate()) >= 6
and f6316 is null
and f1 not like '%lock%'
and f1 not like '%NLNF%'
and f1 not like '%NFNL%'
and f1 not like '%register%'

and '@strDept' = @strUser -- this is the problem portion!

order by datediff(d,f6020,getdate())
End

-- MGR View
if @strDash = '1'
Begin
select
f1 as 'Filename',
f101 as 'Last Name',
f18 as 'Credit Processor',

from all_fields where

foldername in ('active')
and f6315 not in ('1/1/2000')
and datediff(d,f6315,getdate()) >= 6
and f6316 is null
and f1 not like '%lock%'
and f1 not like '%NLNF%'
and f1 not like '%NFNL%'
and f1 not like '%register%'

order by datediff(d,f6020,getdate())
End




As you can see it's pretty ugly for a stored procedure, but thats how the asp pages were originally designed. They stuck the queries right into the procedure and the page calls them. The individual view is what is giving me problems.

Additionally, I didn't write the logic behind the asp page, rather I work with and around it where need be. I'm the only in-house dev at my company and much of this is self-taught over the last few years. I haven't had a formal SQL class yet, so please forgive my lack of best practice.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-21 : 15:20:31
Yikes! :)

Anyway, I did a translation of the first query to D-SQL as a sample:
DECLARE @strDept nvarchar(50) = 'DeptName'
DECLARE @strUser nvarchar(50) = 'UserString'
DECLARE @Sql NVARCHAR(MAX);

SET @Sql = ''
SET @Sql = @Sql + 'select '
SET @Sql = @Sql + ' f1 as Filename,'
SET @Sql = @Sql + ' f101 as [Last Name],'
SET @Sql = @Sql + ' f18 as [Credit Processor],'
SET @Sql = @Sql + 'from '
SET @Sql = @Sql + ' all_fields '
SET @Sql = @Sql + 'where '
SET @Sql = @Sql + ' foldername in (' + CHAR(39) + 'active' + CHAR(39) + ') '
SET @Sql = @Sql + ' and f6315 not in (' + CHAR(39) + '1/1/2000' + CHAR(39) + ') '
SET @Sql = @Sql + ' and datediff(d,f6315,getdate()) >= 6 '
SET @Sql = @Sql + ' and f6316 is null '
SET @Sql = @Sql + ' and f1 not like ' + CHAR(39) + '%lock%' + CHAR(39)
SET @Sql = @Sql + ' and f1 not like ' + CHAR(39) + '%NLNF%' + CHAR(39)
SET @Sql = @Sql + ' and f1 not like ' + CHAR(39) + '%NFNL%' + CHAR(39)
SET @Sql = @Sql + ' and f1 not like ' + CHAR(39) + '%register%' + CHAR(39)
SET @Sql = @Sql + ' and ' + QUOTENAME(@strDept) + ' = ' + QUOTENAME(@strUser, CHAR(39))
SET @Sql = @Sql + ' order by datediff(d,f6020,getdate()) '


SELECT @Sql
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-21 : 15:22:24
Forgot to mention, change the "SELECT @Sql" To "EXEC @Sql" to execute the query.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-21 : 15:29:48
Not sure if you care since that query is going to table scan anyway, but you can replace
order by datediff(d,f6020,getdate())
with
ORDER BY f6020 DESC
That avoid applying a function to the column.
Go to Top of Page

SyDiko
Starting Member

22 Posts

Posted - 2013-08-21 : 15:41:14
Hm.. interesting piece of code.

I do have a few questions if that's okay.

I guess my first is, what is the difference between D-SQl and T-SQL?



Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-21 : 15:47:03
quote:
Originally posted by SyDiko

Hm.. interesting piece of code - I will try as soon as I can and report back. :)

Additionally, what is the difference between D-SQl and T-SQL?

I use D-SQL and short hand for Dynamic-SQL.
Go to Top of Page

SyDiko
Starting Member

22 Posts

Posted - 2013-08-21 : 16:09:52
quote:
Originally posted by Lamprey

quote:
Originally posted by SyDiko

Hm.. interesting piece of code - I will try as soon as I can and report back. :)

Additionally, what is the difference between D-SQl and T-SQL?

I use D-SQL and short hand for Dynamic-SQL.



Gotcha! :)

Okay one more question... What is the purpose of using the char data type to concatenate around the string? And, what exactly is it doing?

SET @Sql = @Sql + ' foldername in (' + CHAR(39) + 'active' + CHAR(39) + ') '
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-21 : 16:19:57
CHAR(39) is the single quote character. Since string literals in SQL Server are defined using single quotes, if your string literal itself includes a single quote, that becomes a problem. You can escape a single quote using another single quote - so for example like this:
SET @Sql = @Sql + '		foldername in (''active'') '
Go to Top of Page

SyDiko
Starting Member

22 Posts

Posted - 2013-08-21 : 16:51:15
quote:
Originally posted by James K

CHAR(39) is the single quote character. Since string literals in SQL Server are defined using single quotes, if your string literal itself includes a single quote, that becomes a problem. You can escape a single quote using another single quote - so for example like this:
SET @Sql = @Sql + '		foldername in (''active'') '




Gotcha, and obviously foldername in(''active'') (which is implicit) will return a syntax error. By explicitly using the char, you avoid that syntax error?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-21 : 17:04:47
No, that is not what I meant. If you run the entire query that Lamprey posted, and then run the same query, except replace that single line with what I posted, you will see that they give the same output. So you can use CHAR(39) or double up on the quotes to escape.

-- replace this line in Lamprey's code
SET @Sql = @Sql + ' foldername in (' + CHAR(39) + 'active' + CHAR(39) + ') '
-- with this line and you should get the same results.
SET @Sql = @Sql + ' foldername in (''active'') '


Go to Top of Page

SyDiko
Starting Member

22 Posts

Posted - 2013-08-21 : 17:30:37
quote:
Originally posted by James K

No, that is not what I meant. If you run the entire query that Lamprey posted, and then run the same query, except replace that single line with what I posted, you will see that they give the same output. So you can use CHAR(39) or double up on the quotes to escape.

-- replace this line in Lamprey's code
SET @Sql = @Sql + ' foldername in (' + CHAR(39) + 'active' + CHAR(39) + ') '
-- with this line and you should get the same results.
SET @Sql = @Sql + ' foldername in (''active'') '






Oooh, okay - kind of see what you mean!

I haven't had a chance to implement... I will try it in the morning when I'm back at work. :)
Go to Top of Page

SyDiko
Starting Member

22 Posts

Posted - 2013-08-22 : 17:08:06
My apologies for a double-post, but I just wanted to reply back with a success!

However, I wasn't able to do the above code, because my predecessor designed the .asp pages to use some funky functions on the front-end when the stored procedure executes. I think that is why I was having trouble with the above - I figured it out earlier this morning because it would work when I execute the procedure from SSMS, but not on the page.

After punching a few holes in a wall, an idea came to me. What I simply did was used this at the end of the original query:

@strUser In (f18,f19,f20,f21, so on and so forth)

this simple piece of code, just saved me hours of work lol. I just pass the username to the IN statement and make the procedure check each name field until it finds a match. This successfully accomplished exactly what I was looking to do!

Its not pretty, but it gets the job done.

Thanks everyone for your help, if it wasn't for you guys - I'd still be trying to figure this out.

Go to Top of Page
   

- Advertisement -