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