0

In my procedure, I need to have input parameters for the table and I need to be able to make multiple select statements into my procedure. However when I begun to add WHERE conditions, they only worked on numerical conditions i.e. where number = 1, but it doesn't work for text = 'a' as the statement already opens with ' ' i.e. 'select...' (see the code)

So the text conditions conflicts with the opening quote mark.

So how do I add this where statement with regards to text?

(The problem is where date is)

The output is the following:

Msg 102, Level 15, State 1, Line 23
Incorrect syntax near '00'.

(53 rows affected)

I've tried to separate the where clause with separate ' ' but no use.

I've tried to use " inside the ' ' but no use, and says 'column name not recognised'

I've tried to treat the text as numeric without ' ' but no use.

CREATE PROCEDURE AllRowsAndCount35
    @table1 NVARCHAR(128)
AS
BEGIN
    DECLARE @SafeTableName AS NVARCHAR(128)

    SELECT @SafeTableName = QUOTENAME(TABLE_NAME)
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_NAME = @table1

    DECLARE @sql AS NVARCHAR(MAX) = 'select  count(*) as countrows from ' + @SafeTableName + 'where valid_from_etl = 2019-06-24 00:00:00.000'

    EXEC(@SQL)

    DECLARE @sql2 AS NVARCHAR(MAX) = 'select  * from ' + @SafeTableName 

    EXEC(@SQL2)
END 

EXEC AllRowsAndCount35 'Dim_Cluster'

I would like a result showing a count of rows, another separate result showing the entire table

Basically two separate queries based off the procedure where I enter the table name as an input parameter

Instead the error message is:

Msg 102, Level 15, State 1, Line 23
Incorrect syntax near '00'.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
codingnoob
  • 49
  • 1
  • 9
  • Use two single quotes (not a double quote) to represent a single quote inside a quoted string. ie `'SELECT... WHERE date = ''date'''`. Or better yet, use a variable like you are for `@safetablename`. That all said, your proc won't return both data sets. You'll have to use a `UNION` to make 1 final select with all the data you want returned, or use parameters to define which query is executed when the proc is called and call the proc twice, or make two separate procs. – Aaron Dietz Sep 26 '19 at 17:54
  • @Aaron, I've used your first bit of advice and it's worked perfectly! – codingnoob Sep 26 '19 at 17:58
  • Glad to hear it, I'll move my comment to an answer you can mark your question answered. Feel free to open another one if you run into any other issues – Aaron Dietz Sep 26 '19 at 18:12

1 Answers1

1

Use two single quotes (not a double quote) to represent a single quote inside a quoted string.

For example: 'SELECT... WHERE date = ''date'''

Or better yet, use a variable like you are for @safetablename.

That all said, your proc won't return both data sets. You'll have to use a UNION to make 1 final select with all the data you want returned, or use parameters to define which query is executed when the proc is called and call the proc twice, or make two separate procs.

Aaron Dietz
  • 10,137
  • 1
  • 14
  • 26