SP input parameter size limitation?

Question

Post reply

SP input parameter size limitation?

rhunt

SSCertifiable

Points: 5868
More actions
September 15, 2005 at 12:21 pm

#95234

I am trying to execute a proc with the statement below:
EXEC dbo.spRyanSearchPropTestWithLand @StatusType='For Lease'
, @RegionID="'627CCF61-56D4-47D8-9E7C-03019E3E4DE1','9F2F3693-4CCA-416A-9E39-034EDB66822C','4A6BD792-7472-4AC8-B373-29B29AED1BEE','191AC990-7668-481D-A34F-4298CC954738','A11CFDBA-A28B-4D88-9816-88FEA32631D5','699F669D-7F77-4421-ADFE-AE163F025B35','5B7D51D8-BAC8-4287-806F-B3F689C9AFDF','C02F04B6-0DE8-4E2C-90D8-C10919F75463','F6C0F46A-3785-4BB1-8960-D0AABF119785','3F78395C-8229-4B2B-88A5-D6B91F7B890F','004EC00F-D666-451E-AE53-E612B1926B33','843D1B88-4755-40BE-99B5-FAD28C4E7C85','02083E9E-08E9-4133-9111-FE7424016A2B'"
, @BuildingType="'Industrial','Office','Retail'"
, @LandType=1
, @SqFtRequested=1000
Yes, @RegionID is long, and it is a string literal being passed to: WHERE regionID IN ("literal")
This works with only a few, but if I try to pass 4 or more I get:
Server: Msg 103, Level 15, State 7, Line 2
The identifier that starts with ''627CCF61-56D4-47D8-9E7C-03019E3E4DE1','9F2F3693-4CCA-416A-9E39-034EDB66822C','4A6BD792-7472-4AC8-B373-29B29AED1BEE','191AC990-7' is too long. Maximum length is 128.
What wall am I hitting? @RegionID is a varchar(1000) being passed as a literal.
A small portion of the proc is below:
CREATE PROCEDURE dbo.spRyanSearchPropTestWithLand
(@StatusType varchar(20)='',
@RegionID varchar(1000)='',
@BuildingType varchar(200)='',
@LandType bit=0,
@SqFtMultiplier varchar(2)='',
@SqFtRequested varchar(20)='')
AS
BEGIN
declare @sql varchar(8000)
declare @SelectSQL varchar(8000)
declare @WhereSQL varchar(8000)
declare @LandSQL varchar(8000)
SET @sql=''
SET @SelectSQL=''
SET @WhereSQL=''
SET @LandSQL=''
IF @SqFtMultiplier = '' BEGIN SET @SqFtMultiplier=8 END
SET @SelectSQL=
'SELECT
b.BuildingID as PropertyID,
b.[Name],
b.BuildingType,
bi.FileName,
a.PostOfficeBox,
a.Street,
a.Street2,
a.Street3,
ac.FirstName,
ac.LastName,
ac.PhoneNumber,
ac.PhoneExt,
dbo.fnGetEarliestDateAvailable(s.BuildingID) as DateAvailable,
s.SuiteNumber,
s.MinSqFt,
s.MaxSqFt
FROM dbo.Buildings b
LEFT OUTER JOIN dbo.Addresses a ON b.AddressID = a.AddressID
LEFT OUTER JOIN dbo.Actors ac ON b.LeasingAgentID = ac.ActorID
LEFT OUTER JOIN dbo.Suites s ON b.BuildingID = s.BuildingID
LEFT OUTER JOIN dbo.BuildingImages bi ON bi.BuildingID=b.BuildingID AND bi.ImageTypeID=2'
SET @WhereSQL=
' WHERE b.StatusType='''+@StatusType
+''' AND b.RegionID IN ('+@RegionID+')'
+' AND s.Viewable='+'''Y'''
-- IF (@PropertyTypeOffice<>'' OR @PropertyTypeRetail<>'' OR @PropertyTypeIndustrial<>'')
-- BEGIN
-- SET @WhereSQL=@WhereSQL
-- +' AND (b.BuildingType='''+@PropertyTypeOffice+''' OR b.BuildingType='''+@PropertyTypeRetail
-- +''' OR b.BuildingType='''+@PropertyTypeIndustrial+''' OR b.BuildingType='''+@PropertyTypeLand+''')'
-- END
IF (@BuildingType<>'')
BEGIN
SET @WhereSQL=@WhereSQL
+' AND b.BuildingType IN ('+@BuildingType+')'
END
IF @SqFtRequested<>''
BEGIN
SET @WhereSQL=@WhereSQL
+' AND s.MinSqFt BETWEEN ('+@SqFtRequested+'-('+@SqFtRequested+'*('+@SqFtMultiplier+'/100.00))) AND ('+@SqFtRequested+'+('+@SqFtRequested+'*('+@SqFtMultiplier+'/100.00)))'
+' AND s.MaxSqFt BETWEEN ('+@SqFtRequested+'-('+@SqFtRequested+'*('+@SqFtMultiplier+'/100.00))) AND ('+@SqFtRequested+'+('+@SqFtRequested+'*('+@SqFtMultiplier+'/100.00)))'
END
SET @sql=@SelectSQL+@WhereSQL+@LandSQL
--EXEC (@SQL)
PRINT @sql
--PRINT LEN(@RegionID)
END
GO

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Try replacing " with ''.

Also read this :

Dynamic Search Conditions in T-SQL

The Curse and Blessings of Dynamic SQL

Arrays and Lists in SQL Server

rhunt SSCertifiable Points: 5868 More actions · Answer 2

Thanks. I read the Dynamic Search Conditions - you and Noeld suggested that one yesterday. I will certainly read the other two.

I tried using singlequote+singleqote instead of double quote, but that didn't do it.

Any ideas why the @RegionID paramter is being limited to 128 characters when it is a varchar(1000)?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Because "" is the quoted identifier, so that gets converted to a sysname (name of an object/column) which internally is nvarchar(128).

Did you try ''' comchar'',''dfdsd;'',''........'''??

Have you considered using a command object to make this call so that you can simply pass the string into a parameter object and not worry about injection and quotes??

Ray M SSC-Insane Points: 21093 More actions · Answer 4

as remi is stating, when your regionid string looks like this

'''627CCF61-56D4-47D8-9E7C-03019E3E4DE1'',''9F2F3693-4CCA-416A-9E39-034EDB66822C'',''4A6BD792-7472-4AC8-B373-29B29AED1BEE'',''191AC990-7668-481D-A34F-4298CC954738'',''A11CFDBA-A28B-4D88-9816-88FEA32631D5'',''699F669D-7F77-4421-ADFE-AE163F025B35'',''5B7D51D8-BAC8-4287-806F-B3F689C9AFDF'',''C02F04B6-0DE8-4E2C-90D8-C10919F75463'',''F6C0F46A-3785-4BB1-8960-D0AABF119785'',''3F78395C-8229-4B2B-88A5-D6B91F7B890F'',''004EC00F-D666-451E-AE53-E612B1926B33'',''843D1B88-4755-40BE-99B5-FAD28C4E7C85'',''02083E9E-08E9-4133-9111-FE7424016A2B'''

it works okay.

these are all apostrophe's (') not Quotes(").