Hi there, I am having a problem with a stored procedure that I am writing. I am fairly new to this so please go easy on me! The stored procedure is:

ALTER PROCEDURE ProviderAttainment

@Attainment varchar(50),
@Gender varchar(50),
@EthCodes varchar(255),
@ResultsBand varchar(50),
@sqlCommand varchar(1000)

AS

SET @sqlcommand ='SELECT DISTINCT Providers.ProviderCounty, Providers.ProviderDistrict, Providers.ProviderName, COUNT(Link.LearnerID) AS Total FROM Learners INNER JOIN
                      Link ON Learners.LearnerID = Link.LearnerID INNER JOIN
                      Providers ON Link.ProviderID = Providers.ProviderID WHERE Learners.' + @Attainment + ' @ResultsBand

AND Learners.Sex = charindex(','+gender+',',@Gender)
AND Learners.EthnicityCodes = charindex(','+EthCodes+',',@EthCodes)
GROUP BY Providers.ProviderCounty, Providers.ProviderDistrict, Providers.ProviderName
ORDER BY Providers.ProviderCounty'

EXEC (@sqlcommand)

The parameters are:

@Attainment - column name in the learners table obtained from a dropdown

@Gender - comma delimited string searching for example ", Male," or ",Male,Female," etc.

@ResultsBand - String containing statements such as "BETWEEN 0 AND 50" or "> 500" etc.

@EthCodes - same as Gender but containing EthnicityCodes

 @sqlCommand  - the SET statement

Could somebody please point me in the right direction? Thank you very much.

>I am having a problem with a stored procedure that I am writing.

What is the problem with your stored procedure?

SET @sqlcommand =

	'SELECT DISTINCT Providers.ProviderCounty, Providers.ProviderDistrict, Providers.ProviderName, 

COUNT(Link.LearnerID) AS Total FROM Learners INNER JOIN
                      Link ON Learners.LearnerID = Link.LearnerID INNER JOIN
                      Providers ON Link.ProviderID = Providers.ProviderID WHERE Learners.' + @Attainment + ' = ''' +@ResultsBand +'''

AND Learners.Sex = charindex(','+gender+',',@Gender) 
AND Learners.EthnicityCodes = charindex(','+EthCodes+',',@EthCodes) 
GROUP BY Providers.ProviderCounty, Providers.ProviderDistrict, Providers.ProviderName
ORDER BY Providers.ProviderCounty'

EXEC (@sqlcommand)

I think you need 2 more variables to do this type of dynamic query because you need the gender to search and the list of available gender!

So, can you try to explain what you want to do with this dyanamic query?? 

mattia

 Also which version of SQL Server are you using?

 Sorry, I should have been clearer. I am using SQL Server 2000. I am attempting to select the number of learners obtaining a particular band of results from a particular column in the Learners table. The link table specfies the combinations of Provider, course and learner so I need to use that to count. There is a table script of the tables here: http://forums.asp.net/t/1282777.aspx.

So the user first selects what attainment criteria they would like to search (dropdown - specifies the column in Learner table), then the results band (dropdown with values such as "BETWEEN 0 AND 50") then a checkboxlist with Male and Female, and finally another checkboxlist with all the possible EthnicityCodes. So I have created methods to concatenate the checkboxlist selections for gender and ethnicity in the code behind page and will pass them to the object data source along with the dropdown values.

 Thank you for responding.

 If code such as ethnicity are mandatory, create a lookup table with a foreign key column. If the column is EthnicId the the TSQL to generate

SELECT A,B,C, FROM Learner WHERE EthnicID IN (1,2,3)

 This means that your Stored procedure needs to have an @EthnicIds VARCHAR(500)

 IF DATALENGTH(@EthnicIds) = 0 SET @EthnicIds = NULL;

IF  @EthnicIds IS NOT NULL
  SET @ARGS = @ARGS + ' AND EthnicId IN (' + @EthnicIds + ') '

 The advantage of this method is that the user selection can be filtered that it is a series of Id values. There needs to be dummy clause like this:#

SET @ARGS = 'SELECT .... FROM tablename WHERE 1=1 '

This means that from 0 to N selections can be AND'd onto @ARGS

 There is a FilterInteger functions I have posted within the CommonData project at http://www.codeplex.com/CommonData/Release/ProjectReleases.aspx?ReleaseId=15049

There is an update to common data at http://www.codeplex.com/CommonData/Release/ProjectReleases.aspx?ReleaseId=16346

* It contains a validation function for address line to ensure that a PO box number is not present.
* A string replication function
* Function to list dates in a given month
* IsAllowedHTML has been extended to include more tags
* IsAllowedHTMLFragment is for part of a page and IsAllowedHTML is for a whole page.
* NUnit has been upgraded to 2.4.8 from 2.4.7
* Code has been optimised for VS2008 using Resharper 4.0

A forthcoming release of CommonData will include the stored procedure for generating stored procedures plus wrapper code.