How can I use a where statement in my procedure with open quotes inside the select statement already?

Question

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'.

Answer 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.