Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS SQL Server 2008 - Proper syntax for a @Sql par...

Posted on 2013-01-04
7
Medium Priority
?
613 Views
Last Modified: 2013-01-04
Hi and thanks,

I am looking for the proper syntax for this statement:
SET @sql = 'SELECT mpropertynumber, ' + @Valnum + ' from [TEMP_PWC_Report_SP_MAIN] Join [TEMP_PWC_Report_SP_CHARACTERISTICS] ON [TEMP_PWC_Report_SP_CHARACTERISTICS].[mpropertynumber] = [TEMP_PWC_Report_SP_MAIN].[mpropertynumber] where' + @Valid1

I get error:
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'TEMP_PWC_Report_SP_'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'TEMP_PWC_Report_SP_'.


I don't think I am joining correctly?

Help and thanks
0
Comment
Question by:Amour22015
[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
7 Comments
 
LVL 11

Assisted Solution

by:Simone B
Simone B earned 668 total points
ID: 38743924
Your syntax seems to be okay, but when you execute @sql, you will get errors if your variables are incorrect. Particulary in the where clause:

WHERE @Valid1

What is the value for this variable?
0
 
LVL 12

Accepted Solution

by:
Saurabh Bhadauria earned 668 total points
ID: 38743926
Can you post your complete code here....


Also check the defined length of @sql variable ....  
make it         Declare @sql   nvarchar(max)




Thanks,
Saurabh
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 664 total points
ID: 38743935
You'll also want to do this to eyeball your SQL statement:

PRINT @sql

>where' + @Valid1
Make sure you have a space between WHERE and the expression, as the absense of a space can throw a syntax error.  Same with the SELECT clause.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Amour22015
ID: 38743957
Ok this is the complete code:
USE [MVP_Demo]
GO
/****** Object:  StoredProcedure [dbo].[TEMP_PWC_VALIDATIONSP]    Script Date: 01/04/2013 10:04:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            
-- Create date: 11/15/2012
-- Description:      Creates a temporary table for Validations
-- =============================================
ALTER PROCEDURE [dbo].[TEMP_PWC_VALIDATIONSP]
AS
BEGIN
IF  EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[TEMP_PWC_VALTEMP]') AND type in (N'U'))
DROP TABLE [dbo].[TEMP_PWC_VALTEMP]

CREATE TABLE [dbo].[TEMP_PWC_VALTEMP]
(
 mpropertynumber varchar(255),
 Valnum varchar(255),
 )

DECLARE @sql nvarchar(4000)
DECLARE @Valnum nvarchar(4000)
DECLARE @Valid1 nvarchar(4000)
DECLARE @Valid2 nvarchar(4000)

DECLARE main_cursor CURSOR FOR
SELECT Valid1, Valid2, Valnum FROM dbo.TEMP_PWC_ValTable
Where CAST(Active as varchar(10)) = '1'
--Where CAST(Valnum as varchar(10)) in ('1', '25')

OPEN main_cursor
SET @sql = ''
SET @Valnum = ''
SET @Valid1 = ''
SET @Valid2 = ''
-- Perform the first fetch.
FETCH NEXT FROM main_cursor
INTO @Valid1, @Valid2, @Valnum


-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
      IF @sql = ''
      BEGIN

            IF @Valid2 = ''
                  SET @sql = 'SELECT mpropertynumber, ' + @Valnum + ' from [TEMP_PWC_Report_SP_MAIN]' Join '[TEMP_PWC_Report_SP_CHARACTERISTICS]' ON '[TEMP_PWC_Report_SP_CHARACTERISTICS].[mpropertynumber] = [TEMP_PWC_Report_SP_MAIN].[mpropertynumber] where' + @Valid1
            ELSE
                  SET @sql = 'SELECT mpropertynumber, ' + @Valnum + ' from [TEMP_PWC_Report_SP_MAIN]' Join '[TEMP_PWC_Report_SP_CHARACTERISTICS]' ON '[TEMP_PWC_Report_SP_CHARACTERISTICS].[mpropertynumber] = [TEMP_PWC_Report_SP_MAIN].[mpropertynumber] where ' + @Valid1 + ' and ' + @Valid2
      END
      ELSE
      BEGIN
            IF @Valid2 = ''
                          SET @sql = @sql +' union ' + 'SELECT mpropertynumber, ' + @Valnum + ' from [TEMP_PWC_Report_SP_MAIN]' Join '[TEMP_PWC_Report_SP_CHARACTERISTICS]' ON '[TEMP_PWC_Report_SP_CHARACTERISTICS].[mpropertynumber] = [TEMP_PWC_Report_SP_MAIN].[mpropertynumber] where ' + @Valid1
            ELSE
                    SET @sql = @sql +' union ' + 'SELECT mpropertynumber, ' + @Valnum + ' from [TEMP_PWC_Report_SP_MAIN]' Join '[TEMP_PWC_Report_SP_CHARACTERISTICS]' ON '[TEMP_PWC_Report_SP_CHARACTERISTICS].[mpropertynumber] = [TEMP_PWC_Report_SP_MAIN].[mpropertynumber] where ' + @Valid1 + ' and ' + @Valid2
      END
   

   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM main_cursor
      INTO @Valid1, @Valid2, @Valnum
     
     
END

CLOSE main_cursor
DEALLOCATE main_cursor

   INSERT [TEMP_PWC_VALTEMP] EXEC sp_executesql @sql
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

END


As you can see I am working on the Join section of the @Sql and again this is not correct.

the fields Valid1 and Valid2 are both functions that check table(s) for conditions example:
Valid1:
[TEMP_PWC_Report_SP_MAIN].[mclassificationid] = '011'

Valid2:
[TEMP_PWC_Report_SP_CHARACTERISTICS].[ExteriorWallCodeCode] Not Between '00' And '72'

What this does is checks the Main table for id to = 011
And
Checks Characteristics for exteriorwallcodecode not between 00 and 72

If both of the conditions are true then a record is added to the Table (ValTemp) using fields: propertyNumbers, and Valnum.


Thanks
0
 
LVL 11

Expert Comment

by:Simone B
ID: 38743977
The keywords JOIN and ON have to be inside the single quotes


ALTER PROCEDURE [dbo].[TEMP_PWC_VALIDATIONSP]
AS
BEGIN
IF  EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[TEMP_PWC_VALTEMP]') AND type in (N'U'))
DROP TABLE [dbo].[TEMP_PWC_VALTEMP]

CREATE TABLE [dbo].[TEMP_PWC_VALTEMP]
(
 mpropertynumber varchar(255),
 Valnum varchar(255),
 )

DECLARE @sql nvarchar(4000)
DECLARE @Valnum nvarchar(4000)
DECLARE @Valid1 nvarchar(4000)
DECLARE @Valid2 nvarchar(4000)

DECLARE main_cursor CURSOR FOR
SELECT Valid1, Valid2, Valnum FROM dbo.TEMP_PWC_ValTable
Where CAST(Active as varchar(10)) = '1'
--Where CAST(Valnum as varchar(10)) in ('1', '25')

OPEN main_cursor
SET @sql = ''
SET @Valnum = ''
SET @Valid1 = ''
SET @Valid2 = ''
-- Perform the first fetch.
FETCH NEXT FROM main_cursor
INTO @Valid1, @Valid2, @Valnum


-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
      IF @sql = ''
      BEGIN

            IF @Valid2 = ''
                  SET @sql = 'SELECT mpropertynumber, ' + @Valnum + ' from [TEMP_PWC_Report_SP_MAIN] Join [TEMP_PWC_Report_SP_CHARACTERISTICS] ON [TEMP_PWC_Report_SP_CHARACTERISTICS].[mpropertynumber] = [TEMP_PWC_Report_SP_MAIN].[mpropertynumber] where' + @Valid1
            ELSE
                  SET @sql = 'SELECT mpropertynumber, ' + @Valnum + ' from [TEMP_PWC_Report_SP_MAIN] Join [TEMP_PWC_Report_SP_CHARACTERISTICS] ON [TEMP_PWC_Report_SP_CHARACTERISTICS].[mpropertynumber] = [TEMP_PWC_Report_SP_MAIN].[mpropertynumber] where ' + @Valid1 + ' and ' + @Valid2
      END
      ELSE
      BEGIN
            IF @Valid2 = ''
                          SET @sql = @sql +' union ' + 'SELECT mpropertynumber, ' + @Valnum + ' from [TEMP_PWC_Report_SP_MAIN] Join [TEMP_PWC_Report_SP_CHARACTERISTICS] ON [TEMP_PWC_Report_SP_CHARACTERISTICS].[mpropertynumber] = [TEMP_PWC_Report_SP_MAIN].[mpropertynumber] where ' + @Valid1
            ELSE
                    SET @sql = @sql +' union ' + 'SELECT mpropertynumber, ' + @Valnum + ' from [TEMP_PWC_Report_SP_MAIN] Join [TEMP_PWC_Report_SP_CHARACTERISTICS] ON [TEMP_PWC_Report_SP_CHARACTERISTICS].[mpropertynumber] = [TEMP_PWC_Report_SP_MAIN].[mpropertynumber] where ' + @Valid1 + ' and ' + @Valid2
      END
   

   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM main_cursor
      INTO @Valid1, @Valid2, @Valnum
     
     
END

CLOSE main_cursor
DEALLOCATE main_cursor

   INSERT [TEMP_PWC_VALTEMP] EXEC sp_executesql @sql
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

END
0
 

Author Comment

by:Amour22015
ID: 38743997
It looks like it is working correctly now, (but I have not tested all conditions yet)

I changed it back to the Join and On inside the quotes

Also add the space after the Where (inside the quote)

and changed to max


Thanks
0
 

Author Closing Comment

by:Amour22015
ID: 38744076
Great thanks
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

610 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