SQL - Structured Query Language (.sql)

Dynamic SQL in StoredProc
	Build a SQL statement in a stored proceedure, and execute it
	CREATE PROCEDURE dbo.bcReportsOMList @state char(5), @sort varchar(10) AS DECLARE @sql varchar(7000) set @sql = ('SELECT bcOfficialMeasurers.OMID, bcUserMain.CustomerID, bcOfficialMeasurers.OMStartDate, bcOfficialMeasurers.OMEndDate, bcOfficialMeasurers.OMNotes, bcOfficialMeasurers.UserMainID, bcOfficialMeasurers.TrainingTypeID, bcUserMain.UserMainID, bcUserMain.UserFName, bcUserMain.UserLName, bcUserMain.UserTitle, bcAddresses.Address, bcAddresses.City, bcAddresses.State, bcAddresses.Zip, bcAddresses.UserMainID, bcPhoneNumbers.UserMainID, bcPhoneNumbers.HomeNumber, bcPhoneNumbers.EmailAddress, bcMenuOMTrainingTypes.TrainingTypeID, bcMenuOMTrainingTypes.TrainingTypeDescription FROM ((((bcUserMain INNER JOIN bcOfficialMeasurers ON bcUserMain.UserMainID = bcOfficialMeasurers.UserMainID) INNER JOIN bcAddresses ON bcUserMain.UserMainID = bcAddresses.UserMainID) INNER JOIN bcPhoneNumbers ON bcUserMain.UserMainID = bcPhoneNumbers.UserMainID) INNER JOIN bcMenuOMTrainingTypes ON bcOfficialMeasurers.TrainingTypeID = bcMenuOMTrainingTypes.TrainingTypeID) ') IF @state <> 'all' BEGIN set @sql = @sql +( ' WHERE bcAddresses.State = ' ) set @sql = @sql + '''' +( rtrim(@state) ) + '''' END IF @sort = 'ByName' set @sql = @sql +( ' ORDER BY bcUserMain.UserLName, bcUserMain.UserFName ' ) ELSE set @sql = @sql +(' ORDER BY bcOfficialMeasurers.OMID' ) execute (@sql) RETURN GO
Go Back