Dynamic SQL in a Srored Procedure

Results 1 to 2 of 2

Thread: Dynamic SQL in a Srored Procedure

  1. #1
    Join Date
    Dec 1969

    Default Dynamic SQL in a Srored Procedure

    Hi Guys,<BR><BR>I&#039;ve written a stored procedure to do a search using dynamic sql within the sproc. I seem to have a problem that the sproc itself is executing, and returning "The command(s) completed successfully." but no records are being returned or empty rows. I&#039;ve checked over the syntax of the sproc and it seems to be fine, I&#039;m lost. Does anyone have any ideas of how I can fix the sproc to return me some results?<BR><BR>********************************** *********************<BR>CREATE PROCEDURE dbo.spSearch <BR> @KeyWords VARCHAR(255),<BR> @iProblemTypeId INT = NULL,<BR> @iProblemAreaId INT= NULL,<BR> @iProductFamilyID INT= NULL,<BR> @vchItemCode VARCHAR(50) = NULL,<BR> @iProductVersion INT= NULL,<BR> @iRelease INT= NULL,<BR> @iOperatingSystemId INT= NULL,<BR> @iMediaId INT= NULL,<BR> @iRoleId INT= NULL, <BR> @iItemTypeId INT= NULL,<BR> @chCountryId CHAR(2) = NULL,<BR> @vchColumn VARCHAR(50) = &#039;Rank&#039;<BR> <BR>AS <BR><BR>DECLARE<BR> @SQLSelect NVARCHAR(2000),<BR> @ColumnParam NVARCHAR(50)<BR><BR> -- Generate the Dynamic SQL query<BR> SELECT @SQLSelect = &#039;SELECT KeyTable.RANK AS Rank, dbo.tItem.vchItemCode AS ItemCode, dbo.tItemContent.vchShortTitle AS Title, <BR> dbo.tItemContent.dtCreated AS [Date], dbo.tRole.vchDescription AS Owner, dbo.tItem.iItemTypeId AS ItemTypeID<BR> FROM dbo.tItemProperties INNER JOIN<BR> dbo.tItemProblemArea ON dbo.tItemProperties.iItemPropertiesId = dbo.tItemProblemArea.iItemPropertiesId INNER JOIN<BR> dbo.tItemOS ON dbo.tItemProperties.iItemPropertiesId = dbo.tItemOS.iItemPropertiesId INNER JOIN<BR> dbo.tItem ON dbo.tItemProperties.iItemPropertiesId = dbo.tItem.iItemPropertiesId INNER JOIN<BR> dbo.tItemStatus ON dbo.tItem.iItemStatusId = dbo.tItemStatus.iItemStatusId INNER JOIN <BR> dbo.tRole ON dbo.tItem.iRoleId = dbo.tRole.iRoleId INNER JOIN<BR> dbo.tItemContent ON dbo.tItem.iItemContentId = dbo.tItemContent.iItemContentId INNER JOIN<BR> CONTAINSTABLE(tItemContent, *, @KeyWords) KeyTable ON dbo.tItemContent.iItemContentId = KeyTable.[KEY] <BR> WHERE (dbo.tItemProperties.iProblemTypeId = COALESCE(@iProblemTypeId, dbo.tItemProperties.iProblemTypeId)) AND <BR> (dbo.tItemProblemArea.iProblemAreaId = COALESCE(@iProblemAreaId, dbo.tItemProblemArea.iProblemAreaId)) AND<BR> (dbo.tItemProperties.iProductFamilyID = COALESCE(@iProductFamilyID, dbo.tItemProperties.iProductFamilyID)) AND <BR> (dbo.tItem.vchItemCode = COALESCE(@vchItemCode, dbo.tItem.vchItemCode)) AND<BR> (dbo.tItemProperties.iRelease = COALESCE(@iRelease, dbo.tItemProperties.iRelease)) AND<BR> (dbo.tItemOS.iOperatingSystemId = COALESCE(@iOperatingSystemId, dbo.tItemOS.iOperatingSystemId)) AND<BR> (dbo.tItemProperties.iMediaId = COALESCE(@iMediaId, dbo.tItemProperties.iMediaId)) AND <BR> (dbo.tItem.iRoleId = COALESCE(@iRoleId, dbo.tItem.iRoleId)) AND<BR> (dbo.tItem.iItemTypeId = COALESCE(@iItemTypeId, dbo.tItem.iItemTypeId)) AND <BR> (dbo.tItemProperties.chCountryId = COALESCE(@chCountryId, dbo.tItemProperties.chCountryId)) AND<BR> (dbo.tItemProperties.iProductVersionId = COALESCE(@iProductVersion, dbo.tItemProperties.iProductVersionId)) <BR> ORDER BY &#039; + @ColumnParam + &#039;&#039;<BR> <BR>BEGIN<BR><BR> EXEC sp_executesql @SQLSelect , N&#039;@ColumnParam VARCHAR&#039;, @vchColumn<BR><BR> RETURN<BR> <BR>END<BR>GO<BR>********************************* **********************<BR><BR>Thanks<BR><BR>Indy

  2. #2
    Join Date
    Dec 1969

    Default RE: Dynamic SQL in a Srored Procedure

    Well... the only problem I see with it is you are trying to use your variables from the calling stored proc in the dynamic sql statement. Can do that. You&#039;ll need to pass each one of those vars as a parameter to the sp_executesql statement ( forgot how ) or do what you did with the @ColumnParam variable and concatinate the values to the string... which may be a bad idea here since you have defaulted them to NULL. If the only reason you need the dynamic sql is for the orderby part with @ColumnParam... you can do this with case else...<BR><BR>Example..<BR><BR>CREATE PROCEDURE dbo.spSearch <BR>@vchColumn VARCHAR(50) = &#039;Rank&#039;<BR>AS <BR><BR><BR>SELECT * FROM SOMETABLE ORDER BY<BR>CASE WHEN @vchColumn = &#039;Rank&#039; THEN Rank END,<BR>CASE WHEN @vchColumn = &#039;Somefield&#039; THEN Somefield END,<BR>CASE WHEN @vchColumn = &#039;Somefield2&#039; THEN Somefield2 END<BR>GO<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts