Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

What is wrong with this query?

Posted on 2010-11-24
11
Medium Priority
?
298 Views
Last Modified: 2012-05-10
I have a SP which I am passing  Vars from a ASP page.

The problem is with the select statement and the var @p4
@p4 is a field name in the table employee.
depending on the product @p4 will change
If I type in the field name this query works fine.
when I try to pass the Var to @p4 it does not.
I have included the ASP script and the SP procedure below.
Thank for your help.
ASP page that passes the Vars to the SP

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="../../Connections/onsa.asp" -->

<%Request.Form("GUID")%>
<%var1 = Request.Form("p1")
Var2 = Request.Form("p2")
Var3 = Request.Form("GUID")
var4 = Request.Form("p4")%>
<%

Dim Command1__p1
Command1__p1 = ""
if(var1    <> "") then Command1__p1 = var1   

Dim Command1__p2
Command1__p2 = ""
if(var2 <> "") then Command1__p2 = var2

Dim Command1__p3
Command1__p3 = ""
if(var3 <> "") then Command1__p3 = var3

Dim Command1__p4
Command1__p4 = ""
if(var4 <> "") then Command1__p4 = var4

%>
<%

set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_onsa_STRING
Command1.CommandText = "dbo.createDL"
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 200, 4,800)
Command1.Parameters.Append Command1.CreateParameter("@p1", 200, 1,4,Command1__p1)
Command1.Parameters.Append Command1.CreateParameter("@p2", 200, 1,1,Command1__p2)
Command1.Parameters.Append Command1.CreateParameter("@p3", 200, 1,9,Command1__p3)
Command1.Parameters.Append Command1.CreateParameter("@p4", 200, 1,14,Command1__p4)
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
%>

Open in new window

The SP itself.

ALTER PROCEDURE [dbo].[createDL] 
-- Add the parameters for the stored procedure here
	@p1 varchar (4),  
	@p2 varchar (1),
	@p3 varchar (9),
	@p4 varchar (14)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT @p1, @p2, @p3, @p4

INSERT temp_email (ID, EMAIL)

SELECT ID, email
FROM employee  
WHERE MTSS_ID = @p1 and @p4 = @p2 

(select * from temp_email)

declare @sqlstr varchar(max),
            @crtid integer, @Getmail varchar (max)

set  @sqlstr =  ''



DECLARE email_list CURSOR FOR (SELECT id  FROM temp_email)
OPEN email_list
      FETCH next FROM email_list INTO @crtid
      WHILE @@fetch_status=0
      BEGIN
            set @sqlstr = @sqlstr + (select email from temp_email where id = @crtid) + ';'
      FETCH next FROM email_list INTO @crtid 
      END
CLOSE email_list
DEALLOCATE email_list
DELETE temp_email
Print @sqlstr
SET @GetMail = @p3+@sqlstr

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBAMail',
	--@from_address = '********@xerox.com',
    @recipients = '**********@Xerox.com',
    @body =  @getmail,
    @subject = 'Get_Mail' ;

END

Open in new window

0
Comment
Question by:xeroxcanada
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34205004
WHERE @p4 = @p2

will compare the values of @p4 and @p2 ...
it will NOT try to understand that @p4's value is the name of a column, and use that column's value to compare.
if you look at it, how should SQL determine that @p2 would not be a column name either ?!

anyhow, to "solve" this, you will either need some dynamic sql, or something like this:

WHERE (  ( @p4 = 'col1' AND col1 = @p2 )
  OR ( @p4 = 'col2' AND col2 = @p2 )
  OR ( @p4 = 'col3' AND col3 = @p2 )
  --- etc for other column names
  )

Open in new window


you realise this is not very "nice"... the dynamic sql is even less nice...
0
 
LVL 2

Author Comment

by:xeroxcanada
ID: 34205032
@p2 is a value that will be in the col  @p4
for instance

SELECT ID, email
FROM employee  
WHERE MTSS_ID =QQ01 and Office_trained = 1
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34205053
as I explained above, the syntax you try will not do what you expect it to do, and this is "by design".
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 2

Author Comment

by:xeroxcanada
ID: 34205060
I am a very big Novice and havn't gotten to the chapter "NICE" yet.  :)
LOL.
Might get there some day but I am afraid I will be retired first :)
0
 
LVL 2

Author Comment

by:xeroxcanada
ID: 34205117
I am trying to compile a list depending on which product the customer.
Each employee is only trained in certain products.
@p4 is that product. See image attached.
@p2 will always be a one, so basiclyy I can remove that Var as it will never change.

@p1 =MTSS_ID and @p4 = one of the fields that the employee is trained in depending on the product from the customer.

How would you SuggestI change the query to accomplish what I need to do.
I Eployee table
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34205185

Are you passing column name in @p2?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34205190
to start with:
>@p1 =MTSS_ID

is not possible, because @p1 is declared as varchar(4), which is too small for that column which is nchar(10) ...

anyhow, let's see if this works for you (some improvements done)

ALTER PROCEDURE [dbo].[createDL] 
-- Add the parameters for the stored procedure here
        @p1 varchar (40),  
        @p2 varchar (1),
        @p3 varchar (9),
        @p4 varchar (14)

AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

declare @sqlstr nvarchar(max)

set @sqlstr = '
INSERT temp_email (ID, EMAIL)
SELECT ID, email
FROM employee  
WHERE MTSS_ID = @p1 
  and [' + @p4 + '] = @p2 
'
exec sp_executesql @sqlstr, N'@p1 varchar(10), @p2 varchar(10)', @p1, @p2

-- (select * from temp_email)

declare @crtid integer
declare @Getmail varchar (max)

select @sqlstr =  coalesce(@sqlstr, '') + email + ';' from temp_email

DELETE temp_email

Print @sqlstr
SET @GetMail = @p3+@sqlstr

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBAMail',
        --@from_address = '********@xerox.com',
    @recipients = '**********@Xerox.com',
    @body =  @getmail,
    @subject = 'Get_Mail' ;

END

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34205203
this said, we can improve even more, avoid the temp_email table:
ALTER PROCEDURE [dbo].[createDL] 
-- Add the parameters for the stored procedure here
        @p1 varchar (40),  
        @p2 varchar (1),
        @p3 varchar (9),
        @p4 varchar (14)

AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

declare @sql nvarchar(max)
declare @sqlstr nvarchar(max)

set @sql = '
SELECT @res = coalesce(@res, '') + email + ';' 
FROM employee  
WHERE MTSS_ID = @p1 
  and [' + @p4 + '] = @p2 
'
exec sp_executesql @sqlstr, N'@p1 varchar(10), @p2 varchar(10), @res varchar(max) OUTPUT ', @p1, @p2, @sqlstr OUTPUT

DECLARE @GetMail varchar(max)
Print @sqlstr
SET @GetMail = @p3+@sqlstr

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBAMail',
        --@from_address = '********@xerox.com',
    @recipients = '**********@Xerox.com',
    @body =  @getmail,
    @subject = 'Get_Mail' ;

END

Open in new window

0
 
LVL 2

Author Comment

by:xeroxcanada
ID: 34205950
Getting the following error on the one from angleIII

Msg 102, Level 15, State 1, Procedure createDL, Line 24
Incorrect syntax near '
FROM employee  
WHERE MTSS_ID = @p1
  and ['.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 34205980
sorry...
ALTER PROCEDURE [dbo].[createDL] 
-- Add the parameters for the stored procedure here
        @p1 varchar (40),  
        @p2 varchar (1),
        @p3 varchar (9),
        @p4 varchar (14)

AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

declare @sql nvarchar(max)
declare @sqlstr nvarchar(max)

set @sql = '
SELECT @res = coalesce(@res, '''') + email + '';'' 
FROM employee  
WHERE MTSS_ID = @p1 
  and [' + @p4 + '] = @p2 
'
exec sp_executesql @sqlstr, N'@p1 varchar(10), @p2 varchar(10), @res varchar(max) OUTPUT ', @p1, @p2, @sqlstr OUTPUT

DECLARE @GetMail varchar(max)
Print @sqlstr
SET @GetMail = @p3+@sqlstr

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBAMail',
        --@from_address = '********@xerox.com',
    @recipients = '**********@Xerox.com',
    @body =  @getmail,
    @subject = 'Get_Mail' ;

END

Open in new window

0
 
LVL 2

Author Closing Comment

by:xeroxcanada
ID: 34229600
Very good Thank YOu :)
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question