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

gvamsimbaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.