Solved

What is wrong with this query?

Posted on 2010-11-24
11
293 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
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 500 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

734 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