Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    10

    Question Unanswered: please help me with procedures and dynamic sql statements

    hi friends
    please give me a complete solution for this
    i had tried quotename but could not rectify the problem
    below is the code:
    create proc proc1 (@cctv char(50)) as
    begin
    DECLARE @cmd varchar(1000)
    set @cmd = 'osql -E -d ' + @cctv + ' -Q "create procedure Dis(@s char(30)) as begin declare @sqls varchar(1000) select @sqls='create table @s(District_ID int NOT NUll,Name char(50),State_id int)' exec(@sqls) end "'
    exec master..xp_cmdshell @cmd, NO_OUTPUT
    end " '

    i want proc1 to create another procedure named dis within a database which is variable proc1 successfully gets the name of the database and then creates dis procedure into the database.
    i am able to create a procedure in a variable database with the above code
    without allowing the dis procedure to accept any variables but when i try to pass variables fro dis procedure then the problem errupts
    please help!!!!!!!!!!
    Last edited by sourav.bhargava@g; 11-25-08 at 07:50.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What problem?
    You haven't mentioned what error you are getting.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2008
    Posts
    10

    the procedure dis does not get created

    when i am using the above code the procedure dis is not getting created .
    however when i am not using any parameters to procedure dis it gets created.
    but i want to create the procedure dis with parameters as mentioned earlier
    so please help me in this regards.
    thanks in advance

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You've got a stored procedure that calls sql to create a dynamic stored procedure that creates a dynamic table.
    And you don't see anything wrong with this design?
    Regardless, here is how to get it to work syntactically:
    Code:
    create proc proc1 (@cctv varchar(128)) as
    begin
    --Test Parameters
    --declare	@cctv varchar(128)
    --set		@cctv = 'TestDB'
    
    DECLARE @cmd varchar(1000)
    set @cmd = 'osql -E -d ' + @cctv + ' -Q "create procedure Dis(@s char(128)) as begin declare @sqls varchar(1000) select @sqls=''create table '' + @s + ''(District_ID int NOT NUll,Name char(50),State_id int)'' exec(@sqls) end "'
    --print @cmd
    exec master..xp_cmdshell @cmd, NO_OUTPUT
    end
    Notice I changed the datatypes to varchar(128), which is sql standard.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2008
    Posts
    10
    Quote Originally Posted by blindman
    You've got a stored procedure that calls sql to create a dynamic stored procedure that creates a dynamic table.
    And you don't see anything wrong with this design?
    Regardless, here is how to get it to work syntactically:
    Code:
    create proc proc1 (@cctv varchar(128)) as
    begin
    --Test Parameters
    --declare	@cctv varchar(128)
    --set		@cctv = 'TestDB'
    
    DECLARE @cmd varchar(1000)
    set @cmd = 'osql -E -d ' + @cctv + ' -Q "create procedure Dis(@s char(128)) as begin declare @sqls varchar(1000) select @sqls=''create table '' + @s + ''(District_ID int NOT NUll,Name char(50),State_id int)'' exec(@sqls) end "'
    --print @cmd
    exec master..xp_cmdshell @cmd, NO_OUTPUT
    end
    Notice I changed the datatypes to varchar(128), which is sql standard.
    Thanks a lot mate . you solved a big problem of mine i have a project to submit at my institute. this was in that regards.
    thanks a lot and have a good time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •