Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

What is wrong with this query?

Posted on 2010-11-24
11
Medium Priority
?
299 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
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

824 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