• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

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

Open in new window

0
gvamsimba
Asked:
gvamsimba
1 Solution
 
PedroCGDCommented:
I was waiting for the response about this...:-)
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
0
 
pssandhuCommented:
You can use the Replace function:
Eg:
Declare @t varchar(20)
SET @t = 'JOHN-DAN'
Select Replace(@t, '-','')
Hope this helps.
P.
0
 
PedroCGDCommented:
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Mark WillsTopic AdvisorCommented:
need to parse @myTable so it is checked to be a correct name...

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'

Open in new window

0
 
PedroCGDCommented:
mark,
You are replacing '-' by ''
The user seems to want a table with name 'db-2' and not 'db2'
Cheers!
Pedro
0
 
Mark WillsTopic AdvisorCommented:
Pedro,

Cannot happen - will error unless encapsulated in [], so, either encapsulate, or need to either parse the table name to "fix" any problems, and/or, trap for errors.

Besides, the Asker has asked how to remove the '-'

So, did both parse and trap for errors...

1) Put in a Try / Catch block so that the error is trapped (and can then do things like ask for a new name etc), rather than allowing the procedure to "crash out"
2) do a replace of '-' to '' and really is not parsing the filename.

Tables names must comply to "Rules for Regular Identifiers" - can look that up in Books On Line.

Now, if the Asker had asked how to accommodate, then would have posted (and arguably better) :

alter PROCEDURE [dbo].[test] (@myTable as varchar(40))
AS
 
DECLARE @SQL nvarchar(max)
 
BEGIN TRY
 
  SET @SQL='CREATE TABLE [' + @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'

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Oh, and then we would still need to parse the table name to make sure no special characters or square brackets were in use...

0
 
jinalCommented:
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 .
0
 
Mark WillsTopic AdvisorCommented:
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 ?
0
 
gvamsimbaAuthor Commented:
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..
0
 
Mark WillsTopic AdvisorCommented:
A Pleasure. Very happy to have been of some assistance...
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now