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
Solved

What is wrong with this query?

Posted on 2010-11-24
11
290 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL replication over high latency link 10 60
SQL STANDARD CORE 7 31
Acces SQL Insert Problem 6 31
SQLCMD Output to file from xpcmd_shell 6 15
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

861 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