October 3, 2007 at 11:28 pm
hi all dear friends,
i am a new bee in this forum group.
i have a need of passing a text query of more than 40000-60000 characters to a stored procedure through a parameter and then have to execute it in a variable for Example:
create proc abc @query text
exec(@query)
but,
i am not getting the how to do it,
i know you people must be knowing the limitations with text variables,
but i have to do it any how ..
Plz, you all people help me out as soon as possible.
Thanks in Advance.
Regards,
Praveen Satti (Delhi,India)
October 4, 2007 at 12:12 am
Change your TEXT parameter to the new VARCHAR(max).
N 56°04'39.16"
E 12°55'05.25"
October 4, 2007 at 12:15 am
Since you're working with SQL 2005 (or I assume so, based on the forum) don't use text. It's deprecated. Rather use the varchar(max) datatype.
Also, what you're doing is very strange. why don't you just execute the string? Why take the effort to build up a stored proc that just executes a string?
That's completely ignoring all the issues around dynamic sql, including permissions, sql injection and cache-bloat (see http://www.sommarskog.se/dynamic_sql.html)
That said
-- create the proc
create procedure abc @query varchar(max)
AS
execute(@query)
GO
-- run the proc
DECLARE @ShortQuery VARCHAR(max)
SET @shortQuery = 'select * from sys.objects'
EXEC abc @shortQuery
GO
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2007 at 6:19 am
May be i didnt explained the query exactly actually i have a very big query to be written in the stored procedure which takes a lot of parameters and text parameter @Query is one of them ,
but i am explaining only the exec(@Query) part of that thing which i have to use at the end of procedure just before commiting this is exactly what i require.
so, i cannot write the whole of the stored procedure and other thing to be noticed is that i am currently working on the SQL Server 2000
so varchar(Max) is not possible.
Plz Tell me how to overcome this.
October 4, 2007 at 6:28 am
If you're working on SQL 2000, please rather post in the SQL 2000 forums. If you post in SQL 2005 forums, people will offer 2005-specific solutions.
I'm not understanding your problem. What is it you can't do?
You don't need to be able to declare a text parameter to execute a text string.
CREATE PROCEDURE execBig @longQuery TEXT
AS
EXECUTE (@longQuery)
GO
EXEC execBig 'Select * from sysobjects'
You can also concatinate pieces of string together when using exec
CREATE PROCEDURE execBig2 @Bit1 varchar(8000), @Bit2 varchar(8000), @Bit3 varchar(8000)
AS
EXECUTE (@Bit1 + @Bit2 + @Bit3)
GO
EXEC execBig2 'Select *', ' From ', 'sysobjects'
What am I missing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2007 at 7:16 am
hey may be the second solution provided by you as concatenation of more than on varchar variables work out for me may be i hav got the hint so let i try this once.
Thanx, for the advice
Thank you very much.
I'll be here again if it doesn't work
thanx alot,
Regards
Praveen Satti
October 5, 2007 at 5:27 am
You could also create a SQL_STATEMENTS TABLE with an id text and link_id... Pass the id to the stored procedure and you can reproduce the SQL there with for example a function.. this way you can use it in any SP you like
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply