Solved Not accept Parameters (1 Viewer)

[zezo2021]

Why does this code not accept the parameters inside SQL create view from the stored procedure?

I want to pass the value of stored procedure parameters to create a view

Create PROCEDURE MyProcedure
@ID int
AS

BEGIN
-- if object_id ('myview' ,'v') is not null
DROP VIEW IF EXISTS myview

    DECLARE @viewName VARCHAR(100) = 'MyView';
    DECLARE @viewSQL NVARCHAR(MAX) = 'CREATE VIEW ' + @viewName + ' AS SELECT * FROM Client' + 'where ID=@ID';

    EXEC sp_executesql @viewSQL;
END;

 

[theDBguy]

It looks like your string concatenation may be a bit off.

 

[zezo2021]

AI suggestion

CREATE PROCEDURE [dbo].[MyProcedure]
@ID int
AS
BEGIN
-- if object_id ('myview' ,'v') is not null
DROP VIEW IF EXISTS myview

DECLARE @sql nvarchar(max)

SET @sql = 'CREATE VIEW dbo.myview AS
        SELECT dbo.Client.ClientName, dbo.Client.ID
        FROM client
        WHERE Client.ID = @ID;'

EXEC sp_executesql @sql, N'@ID int', @ID
END

but not work also

 

[zezo2021]

this error appear
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'VIEW'.

 

[cheekybuddha]

Why are you trying to do this in the first place?

 

[zezo2021]

Why are you trying to do this in the first place?

link access to Microsoft SQL Server view

 

[cheekybuddha]

Just create the query in Access

It will be just as efficient as creating the view on the server.

 

[sonic8]

It looks like your string concatenation may be a bit off.

@zezo2021 , why are you ignoring this hint? It's spot on. Your concatenation of @viewName is correct, but that of @ID is not.

 

[sonic8]

AI suggestion

This is junk. You cannot pass parameters to DDL statements.

 

[zezo2021]

@zezo2021 , why are you ignoring this hint? It's spot on. Your concatenation of @viewName is correct, but that of @ID is not.

No, ignore but I don't now what can I do

 

[sonic8]

but I don't now what can I do

You're embedding the parameter literal @ID into to the SQL, not it's value. Also there is a blank missing between the table name and the WHERE keyword.

 

[zezo2021]

error
Conversion failed when converting the varchar value 'CREATE VIEW MyView AS SELECT * FROM Client where Client.ID=' to data type int.

I do this
DECLARE @viewSQL NVARCHAR(MAX) = 'CREATE VIEW ' + @viewName + ' AS SELECT * FROM Client' + 'where ID='+@ID;

 

[sonic8]

Conversion failed when converting the varchar value 'CREATE VIEW MyView AS SELECT * FROM Client where Client.ID=' to data type int.

You're almost there.
The error happens because SQL Server tries to use the + operator for a mathematical addition unless all operands are of a text data type. To prevent this, you need to explicitly convert @ID to text.

... + ' where ID='+ convert(nvarchar,@ID);
--    ^^

Mind the blank I added before the Where! That addresses the other problem with your string concatenation I mentioned.

 

[zezo2021]

You're almost there.
The error happens because SQL Server tries to use the + operator for a mathematical addition unless all operands are of a text data type. To prevent this, you need to explicitly convert @ID to text.

... + ' where ID='+ convert(nvarchar,@ID);
--    ^^

Mind the blank I added before the Where! That addresses the other problem with your string concatenation I mentioned.

best