July 28, 2010 at 8:26 am
Is there any way to store a store procedure as a record in one table cell?
The problem is that only one line can be copied.
For example, copy sp1 below into a cell will be "SET ANSI_NULLS ON" only.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP1]
SELECT * FROM ORDER
July 28, 2010 at 8:53 am
Try this
CREATE TABLE QTest(PR VARCHAR(MAX))
INSERT INTO QTEST
SELECT 'SELECT SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP1]
SELECT * FROM ORDER'
SELECT PR FROM Qtest (Displaying in text format using SSMS) gives:
SELECT SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP1]
SELECT * FROM ORDER
And if your T-SQL contains single quote ('), they will have to be doubled ('') For example:
SELECT
'DECLARE @ANSI INT
DECLARE @ANSW VARCHAR(6)
SET @ANSI = CONVERT(INT,DATABASEPROPERTYEX(''Test'',''isansipaddingenabled''))
IF @ANSI = 1
BEGIN
SET @ANSW = ''FALSE''
END
IF @ANSI = 0
BEGIN
SET @ANSW = ''TRUE''
END
SELECT @ANSW'
July 28, 2010 at 9:20 am
Why would you want to store them twice, they are already being stored in a table.
SELECT
p.name
,c.Text
FROM
syscomments c
JOIN
sys.procedures p
ON
c.id = p.object_id
Andrew SQLDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply