Solved

Dynamic SQL proc

Posted on 2009-07-10
11
213 Views
Last Modified: 2013-11-10
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
Comment
Question by:gvamsimba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24824761
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
 
LVL 17

Expert Comment

by:pssandhu
ID: 24824947
You can use the Replace function:
Eg:
Declare @t varchar(20)
SET @t = 'JOHN-DAN'
Select Replace(@t, '-','')
Hope this helps.
P.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24825017
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 24825155
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24825192
mark,
You are replacing '-' by ''
The user seems to want a table with name 'db-2' and not 'db2'
Cheers!
Pedro
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 24825950
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24825979
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
 
LVL 15

Expert Comment

by:jinal
ID: 24829042
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24829114
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
 

Author Closing Comment

by:gvamsimba
ID: 31602130
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24840718
A Pleasure. Very happy to have been of some assistance...
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to resize column length with primary ket 4 34
I have an unknown large SQL database I want to get rid of 10 47
What does "Between" mean? 6 49
t-sql left join 2 36
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question