January 10, 2015 at 11:24 am
Hi,
Require help in passing parameters to a stored proc for ex:
GO
create procedure [dbo].[sp_create_proc]
(
@v_in_dbnm varchar(255),
@v_in_schemanm varchar(30)
)
as
BEGIN
DECLARE @v_in_dbname nvarchar(255)
DECLARE @v_in_schemaname nvarchar(30)
DECLARE @sql nvarchar(MAX)
SET @v_in_dbname = @v_in_dbnm
SET @v_in_schemaname = @v_in_schemanm
SET @sql='CREATE VIEW vw_name AS
(
SELECT
y.col1,
y.col2,
(select colx from @v_in_dbname.@v_in_schemaname.tablename x where x.col1 = y.col1)as col3
from sometable y
)'
EXECUTE sp_executesql @sql
END
GO
during execute procedure call i get an error message variable @v_in_dbname has not been declared.
January 10, 2015 at 11:57 am
ravikumar_v (1/10/2015)
Hi,Require help in passing parameters to a stored proc for ex:
GO
create procedure [dbo].[sp_create_proc]
(
@v_in_dbnm varchar(255),
@v_in_schemanm varchar(30)
)
as
BEGIN
DECLARE @v_in_dbname nvarchar(255)
DECLARE @v_in_schemaname nvarchar(30)
DECLARE @sql nvarchar(MAX)
SET @v_in_dbname = @v_in_dbnm
SET @v_in_schemaname = @v_in_schemanm
SET @sql='CREATE VIEW vw_name AS
(
SELECT
y.col1,
y.col2,
(select colx from @v_in_dbname.@v_in_schemaname.tablename x where x.col1 = y.col1)as col3
from sometable y
)'
EXECUTE sp_executesql @sql
END
GO
during execute procedure call i get an error message variable @v_in_dbname has not been declared.
Some elementary flaws in the code, first of all, a dynamic statement is at the time of creation only a string, does not resolve variable values hence one needs either to use replace or concatenation, here is an example
😎
SET @sql=N'CREATE VIEW vw_name AS
(
SELECT
y.col1,
y.col2,
(select colx from ' + @v_in_dbname + N'.' + @v_in_schemaname + N'.tablename x where x.col1 = y.col1)as col3
from sometable y
)'
The next thing is the missing object reference, cannot imagine that every database has a table called "tablename", where does that come from?
Third/last but not least, what are you going to do when someone calls the procedure with the parameter "(select null) ) as y; DROP DATABASE/TABLE/YOUR JOB;"?
January 10, 2015 at 9:29 pm
Thank you so much Eiriksson. your observations and responses have helped me resolve the issue I was facing.
It was silly of me to have overlooked the very obvious "dynamic statement is at the time of creation only a string, does not resolve variable values " .
With ref. to your 2nd question "missing object reference, cannot imagine that every database has a table called "tablename", where does that come from?"
- "tablename" was a placeholder in the posted code snippet, the original code snippet contains a valid tablename.
With ref to your 3rd question "what are you going to do when someone calls the procedure with the parameter"
- I was planning to convert the proc into a function call and supply parameters via the information_schema.tables
Warm Regards,
Ravikumar V
..
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply