January 6, 2010 at 9:48 am
I am using SQL 2008. I have a stored procedure that I am passing 2 parameters into. One parameter is an integer and the other is a string. I have the string defined as NTEXT. When I call this procedure from my VB code, I am not getting the expected results (if string passed in is 250 characters or less, I get the correct results) because for some reason my NTEXT field was not read in its entirety. Can someone help me see what I am doing wrong?
CREATE PROCEDURE [dbo].[GCI_PM_FileSubTypeGetBy_FileTypeAndContracts]
@FileTypeID INT,
@Contracts ntext
AS
EXEC('SELECT DISTINCT
GCI_PM_FileSubType.ID, GCI_PM_FileSubType.SubTypeName, GCI_PM_FileSubType.FileTypeID, GCI_PM_FileSubType.CreateDate,
GCI_PM_FileSubType.CreateUserID, GCI_PM_FileSubType.ModifyDate, GCI_PM_FileSubType.ModifyUserID,
CreateUsers.FirstName + '' '' + CreateUsers.LastName AS CreateUser, ModifyUsers.FirstName + '' '' + ModifyUsers.LastName AS ModifyUser,
GCI_PM_FileType.FileTypeName
FROM GCI_PM_FileSubType WITH (NOLOCK) INNER JOIN
GCI_PM_FileSubTypeXContract ON GCI_PM_FileSubType.ID = GCI_PM_FileSubTypeXContract.FileSubTypeID LEFT OUTER JOIN
GCI_PM_FileType WITH (NOLOCK) ON GCI_PM_FileSubType.FileTypeID = GCI_PM_FileType.ID LEFT OUTER JOIN
Users AS ModifyUsers WITH (NOLOCK) ON GCI_PM_FileSubType.ModifyUserID = ModifyUsers.UserID LEFT OUTER JOIN
Users AS CreateUsers WITH (NOLOCK) ON GCI_PM_FileSubType.CreateUserID = CreateUsers.UserID
WHERE (GCI_PM_FileSubType.ID IN
(SELECT FileSubTypeID
FROM GCI_PM_FileSubTypeXContract AS GCI_PM_FileSubTypeXContract_1
WHERE (GCI_PM_FileSubTypeXContract.ContractID IN (' + @Contracts + ')))) AND (GCI_PM_FileSubType.FileTypeID = ' + @FileTypeID + ')')
GO
January 6, 2010 at 10:59 am
It is hard to tell without seeing some sample data,
however what data type is GCI_PM_FileSubTypeXContract.ContractID ?
January 6, 2010 at 11:20 am
ContractID is an integer.
sample data:
(51, 53, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178)
January 7, 2010 at 2:58 am
Why are you then declaring @Contracts as a unicode text data type?
if thre are only integers then you wont need to use unicode so it would be better to use a varchar data type
January 8, 2010 at 8:18 am
Since you are using SQL 2008 you can use a TVP to pass the integer data
CREATE TYPE dbo.InputContractsTable AS TABLE
(
Contracts int NOT NULL
)
GO
CREATE PROCEDURE [dbo].[GCI_PM_FileSubTypeGetBy_FileTypeAndContracts]
@FileTypeID INT,
@parInputContractsTable InputContractsTable READONLY
Now you can just use a join to access the input values.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply