gvamsimba
asked on
Dynamic SQL proc
Hi,below is my stored proc in dynmaic sql. i am actually
calling this into my SSIS package which is importing
text files into the DB.. some file names are like
db-1..so my package is throwing an error saying
Incorrect syntax near '-'.
I know it is bcos of that - sign ..can anybody
make the change to my proc so that if the filename
is db-1, it shuld change it to db1 on the fly..so basically
just removing the '-'.
Many Thanks
calling this into my SSIS package which is importing
text files into the DB.. some file names are like
db-1..so my package is throwing an error saying
Incorrect syntax near '-'.
I know it is bcos of that - sign ..can anybody
make the change to my proc so that if the filename
is db-1, it shuld change it to db1 on the fly..so basically
just removing the '-'.
Many Thanks
ALTER PROCEDURE [dbo].[test]
@myTable as varchar(40)
AS
DECLARE @SQL nvarchar(max)
SET @SQL='CREATE TABLE ' + @myTable + '
([Address ] [nvarchar](255) NULL,
[Name1] [nvarchar](255) NULL,
[Name2] [nvarchar](255) NULL,
) ON [PRIMARY]'
exec sp_executesql @SQL
You can use the Replace function:
Eg:
Declare @t varchar(20)
SET @t = 'JOHN-DAN'
Select Replace(@t, '-','')
Hope this helps.
P.
Eg:
Declare @t varchar(20)
SET @t = 'JOHN-DAN'
Select Replace(@t, '-','')
Hope this helps.
P.
but the user wants '-'...
vamsim,
If you dont need '-' you can change it directly in SSIS package, dont need to do in SQL...
Give feedback
vamsim,
If you dont need '-' you can change it directly in SSIS package, dont need to do in SQL...
Give feedback
need to parse @myTable so it is checked to be a correct name...
Also would be worthwhile putting in a TRY CATCH block...
Also would be worthwhile putting in a TRY CATCH block...
alter PROCEDURE [dbo].[test] (@myTable as varchar(40))
AS
DECLARE @SQL nvarchar(max)
BEGIN TRY
SET @SQL='CREATE TABLE ' + replace(@myTable,'-','') + '
([Address ] [nvarchar](255) NULL,
[Name1] [nvarchar](255) NULL,
[Name2] [nvarchar](255) NULL,
) ON [PRIMARY]'
exec sp_executesql @SQL
SELECT 'SUCCESS' as STATUS, 0 as StatusNumber
END TRY
BEGIN CATCH
-- error handling message
SELECT 'ERROR ENCOUNTERED' as STATUS,
ERROR_NUMBER() AS StatusNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO
-- Then try it
test 'db-2'
-- and again
test 'db-2'
mark,
You are replacing '-' by ''
The user seems to want a table with name 'db-2' and not 'db2'
Cheers!
Pedro
You are replacing '-' by ''
The user seems to want a table with name 'db-2' and not 'db2'
Cheers!
Pedro
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh, and then we would still need to parse the table name to make sure no special characters or square brackets were in use...
ALTER PROCEDURE [dbo].[test]
@myTable as varchar(40)
AS
BEGIN
DECLARE @SQL nvarchar(max)
SET @SQL='CREATE TABLE [' + @myTable + ']
([Address ] [nvarchar](255) NULL,
[Name1] [nvarchar](255) NULL,
[Name2] [nvarchar](255) NULL,
) ON [PRIMARY]'
exec sp_executesql @SQL
END
EXEC test 'db-2'
Now it works .
@myTable as varchar(40)
AS
BEGIN
DECLARE @SQL nvarchar(max)
SET @SQL='CREATE TABLE [' + @myTable + ']
([Address ] [nvarchar](255) NULL,
[Name1] [nvarchar](255) NULL,
[Name2] [nvarchar](255) NULL,
) ON [PRIMARY]'
exec sp_executesql @SQL
END
EXEC test 'db-2'
Now it works .
jinal,
and how is that any different to what I was saying above ?
and how does that remove the '-' as was the original request ?
then... test yours with : test ']db-3'
then... test mine with : test ']db-3'
Which one crashes, which one doesn't crash but returns a status instead ?
That is what I was meaning about parsing the name properly and/or add error handling in there. Maybe you didn't see my previous entry ?
and how is that any different to what I was saying above ?
and how does that remove the '-' as was the original request ?
then... test yours with : test ']db-3'
then... test mine with : test ']db-3'
Which one crashes, which one doesn't crash but returns a status instead ?
That is what I was meaning about parsing the name properly and/or add error handling in there. Maybe you didn't see my previous entry ?
ASKER
this replacement makes more sense..i have modified this
in my SP and my ssis package and it gave me exactly
what i want..thank u very much..
in my SP and my ssis package and it gave me exactly
what i want..thank u very much..
A Pleasure. Very happy to have been of some assistance...
You need to maintain "-" in the name, correct?
I will try to do that in the query... just a moment
Regards,
Pedro
www.pedrocgd.blogspot.com
www.BIResort.net