Solved

What is wrong with this query?

Posted on 2010-11-24
11
282 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 142

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 142

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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34205185

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

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 142

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 142

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
c# code 19 59
Trouble connecting to SqlServer database 4 32
SQL Server 2012 Row Selection 2 30
Sql query to Stored Procedure 6 20
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now