Wednesday, February 15, 2012

Dynamic SQL Expert pleaaaaaaaase

First i am designing a small database fo similar types of books ( Stories table , Short stories table , ...etc ) so i the columns in each table of them is similar , so instead of using a Stored Procedure for every table to select the TOP book i used one SP with variable table name and it works well as i pass the table name from the page , here it is :
--------------
CREATE PROCEDURE Top1
@.tblname Varchar(255)
AS

EXEC ( 'SELECT TOP 1 *
FROM ' + @.tblname + '
WHERE Status="OLD"
ORDER BY ID DESC')
GO
---------------
The Problem now that i want to make the same thing with the INSERT STATEMENT to input new books to the table by passing the table name from the page like the SELECT one , and i `ve tried this one but gives me errors :
------------------------------
CREATE PROCEDURE AddNewStory
@.tblname varchar(50),
@.title varchar(50),
@.Content varchar(5000) ,
@.Picture varchar(200) ,
@.date datetime
AS

EXEC('INSERT INTO' + @.tblname + '( Sttitle , StContent, StPic, StDate )
VALUES ( '+ @.Title + ',' + @.Content+ ',' + @.Picture+ ',' + @.date + ')' )
GO
------------------------------

& th error is

Server: Msg 128, Level 15, State 1, Line 2
The name 'Dickens' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
Stored Procedure: db1sql.dbo.AddNewStory
Return Code = 0

SO PLEASE HELPPPPPPPPPPPHi,

I just give you some suggests, I am not sure them will sove your problems,

First you could write the Stored Procedure like this:

CREATE PROCEDURE AddNewStory
@.tblname varchar(50),
@.title varchar(50),
@.Content varchar(5000) ,
@.Picture varchar(200) ,
@.date datetime
AS

SET NOCOUNT ON

INSERT @.tblname (
Sttitle,
StContent,
StPic,
StDate )

SELECT @.Title,
@.Content,
@.Picture,
@.date

GO|||You're not getting the single quotes in where they need to be.


declare @.sql varchar(4000)

set @.sql = 'INSERT INTO' + @.tblname + ' ( Sttitle , StContent, StPic, StDate )
VALUES ( '''+ @.Title + ''',''' + @.Content+ ''',''' + @.Picture+ ''',''' + @.date + ''')'

EXEC(@.sql)

I always build a string first and then exec that. Makes it much easier to debug, because you can just write out your string in QA and see what's wrong with it. Had you done that, it would have been very obvious the quotes were missing.|||Thank you too much ,for your help , its working well , i was searching for this solution for a whole day without any answer till asking you in this good forum ,
There is very small problem , and of course u have an answer for it ,

After writing this Query :
------------------------
CREATE PROCEDURE AddNewStory
@.tblname varchar(50),
@.title varchar(50),
@.Content varchar(5000) ,
@.Picture varchar(200),
@.date datetime
AS

declare @.sql varchar(4000)

set @.sql = 'INSERT INTO' + @.tblname + ' ( Sttitle , StContent, StPic, StDate )
VALUES ( '''+ @.Title + ''',''' + @.Content+ ''',''' + @.Picture+ ''',''' + @.date + ''')'

EXEC(@.sql)
GO
------------------------
Every thing worked well but without the Date , it gives an error :
--------
error converting datetime from character string.
--------
as the @.date variable is a datetime variable in my aspx page and Datetime variable also in the Databas Column , , it worked only when i used a static table name query like this :
----------------
CREATE PROCEDURE AddNewStory44
@.Title varchar(50) ,
@.Content varchar(5000) ,
@.Picture varchar(200) ,
@.date datetime
AS
INSERT INTO tblstory ( StTitle, StContent, StPic, StDate )
VALUES (@.Title, @.Content,@.Picture,@.date)
GO
----------------
but i want Your Dynamic solution , And thank u again
Waiting for your Solution for the date error|||You could do this:


set @.sql = 'INSERT INTO' + @.tblname + ' ( Sttitle , StContent, StPic, StDate )
VALUES ( '''+ @.Title + ''',''' + @.Content+ ''',''' + @.Picture+ ''',''' + CONVERT(varchar(20),@.date,112) + ''')'

EXEC(@.sql)

This converts the date to a string, since you are building up a string.

No comments:

Post a Comment