What is wrong with this query?

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

LVL 2
xeroxcanadaAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
xeroxcanadaAuthor Commented:
@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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as I explained above, the syntax you try will not do what you expect it to do, and this is "by design".
0
 
xeroxcanadaAuthor Commented:
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
 
xeroxcanadaAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:

Are you passing column name in @p2?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
xeroxcanadaAuthor Commented:
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
 
xeroxcanadaAuthor Commented:
Very good Thank YOu :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.