September 27, 2002 at 9:34 am
the contents of a Field from a table. I have a web interface that allows users to create and modify SQL scripts. The updates are stored in a field in one of my tables (datatype text). What I want to be able to do is:
Create a stored procedure that looks at the SQL statement from the above table field. This way users can modify these scripts, then a stored procedures gets ran using a SQL job and uses the value from my table. I have tried the following however the issue is that One my SQL query coming from my table is more than 4000 char long and its also of data type TEXT so that it can’t be converted in a Declared variable:
CREATE PROCEDURE [dbo].[SP_Query] AS
DECLARE @longSQLString varchar(4000)
SELECT query FROM TransformationTable WHERE ID = 1
SELECT @longSQLString
The other though that I had was that when the users updates there script it actually drops and recreates the stored procedure using ADO on the ASP web page. Example:
Dim sp_Name
Sp_Name = Request(“sp_Name”)
Dim transcript
transcript = Request(“transcript”) ‘ Long SQL Query for stored procedure
dropSp = "if exists (select * from dbo.sysobjects where id = object_id(N'[gripaWeb_writer].[" & sp_name & "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [gripaWeb_writer].[" & sp_name & "]"
connSp.Execute(dropSp)
createSP = "CREATE PROCEDURE [gripaWeb_writer].[" & sp_name & "] AS "_
& transScript & ""
connSp.Execute(createSP)
The problem there is that it bombs out for some reason or another. The sql code contains Go’s and I don’t think it likes it, however If I run the script in analyzer Go’s do not present any issues even if I paste the SQL into a new stored procedure using Enterprise Manager.
Any help would help.
Edited by - kbrady on 09/27/2002 09:39:12 AM
September 27, 2002 at 10:44 am
Instead of using GOs, what about issueing multiple executes?
Steve Jones
September 27, 2002 at 11:03 am
GO is a QA construct. Try separate executes or just delimit with a semi colon.
Andy
October 1, 2002 at 3:00 pm
First I think you should check out AdoDB.Stream class and examples for updates/reads in text fields
Second, avoid transactions!, you have many ways: autocommit=true for example. Because transactions will impose limitations in your object creation/recreation needs
Third: Don forget 'Alter Procedure' as an option instead the pair : if exists, create proc..
quote:
the contents of a Field from a table. I have a web interface that allows users to create and modify SQL scripts. The updates are stored in a field in one of my tables (datatype text). What I want to be able to do is:Create a stored procedure that looks at the SQL statement from the above table field. This way users can modify these scripts, then a stored procedures gets ran using a SQL job and uses the value from my table. I have tried the following however the issue is that One my SQL query coming from my table is more than 4000 char long and its also of data type TEXT so that it can’t be converted in a Declared variable:
CREATE PROCEDURE [dbo].[SP_Query] AS
DECLARE @longSQLString varchar(4000)
SELECT query FROM TransformationTable WHERE ID = 1
SELECT @longSQLString
The other though that I had was that when the users updates there script it actually drops and recreates the stored procedure using ADO on the ASP web page. Example:
Dim sp_Name
Sp_Name = Request(“sp_Name”)
Dim transcript
transcript = Request(“transcript”) ‘ Long SQL Query for stored procedure
dropSp = "if exists (select * from dbo.sysobjects where id = object_id(N'[gripaWeb_writer].[" & sp_name & "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [gripaWeb_writer].[" & sp_name & "]"
connSp.Execute(dropSp)
createSP = "CREATE PROCEDURE [gripaWeb_writer].[" & sp_name & "] AS "_
& transScript & ""
connSp.Execute(createSP)
The problem there is that it bombs out for some reason or another. The sql code contains Go’s and I don’t think it likes it, however If I run the script in analyzer Go’s do not present any issues even if I paste the SQL into a new stored procedure using Enterprise Manager.
Any help would help.
Edited by - kbrady on 09/27/2002 09:39:12 AM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply