السلام عليكم ورحمة الله وبركاته
قى هذا البروسيدجر كيف أقوم بإسترجاع قيمة المتغير@sqlString
لإستخدامه فى إنشاء جدول إستعلام بالكود فى asp
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE WY_CMP_HOSPITAL_search
@h_hs INT,
@hospitalId INT,
@hs_operation INT,
@hs_region INT,
@hs_country INT,
@hs_zone INT,
@hs_city INT,
@hs_category INT,
@hs_type INT,
@hs_trial INT,
@hs_formulary INT,
@searchstring VARCHAR(200),
@hs_indication INT,
@rt_hospital VARCHAR(5000),
@status INT,
@orderBy TINYINT,
@desc_order BIT
AS
BEGIN
DECLARE
@where_cond VARCHAR(1000),
@orderByString VARCHAR(1500),
@sqlString VARCHAR(8000)
IF @status > 0
SET @where_cond = ' where status = ' + CONVERT(VARCHAR,@status)
ELSE
SET @where_cond = ' where (status = 1 Or status = 2)'
-- Default orderByString when @orderBy = 0
SET @orderByString = 'hospitalId'
IF @rt_hospital <> '0'
SET @where_cond = @where_cond + ' and hospitalid in (' + @rt_hospital + ')'
IF @hospitalId > 0
SET @where_cond = @where_cond + ' and hospitalid = ' + CONVERT(VARCHAR,@hospitalId)
IF @hs_operation > 0
SET @where_cond = @where_cond + ' and hs_operation = ' + CONVERT(VARCHAR,@hs_operation)
IF @hs_region > 0
SET @where_cond = @where_cond + ' and hs_region = ' + CONVERT(VARCHAR,@hs_region)
IF @hs_country > 0
SET @where_cond = @where_cond + ' and hs_country = ' + CONVERT(VARCHAR,@hs_country)
IF @hs_zone > 0
SET @where_cond = @where_cond + ' and hs_zone= ' + CONVERT(VARCHAR,@hs_zone)
IF @hs_city > 0
SET @where_cond = @where_cond + ' and hs_city = ' + CONVERT(VARCHAR,@hs_city)
IF @hs_city > 0
SET @where_cond = @where_cond + ' and hs_city = ' + CONVERT(VARCHAR,@hs_city)
IF @hs_category > 0
SET @where_cond = @where_cond + ' and hs_category = ' + CONVERT(VARCHAR,@hs_category)
IF @hs_type > 0
SET @where_cond = @where_cond + ' and hs_type = ' + CONVERT(VARCHAR,@hs_type)
IF @hs_trial > 0
SET @where_cond = @where_cond + ' and hs_trial = ' + CONVERT(VARCHAR,@hs_trial)
IF @hs_formulary > 0
SET @where_cond = @where_cond + ' and hs_formulary = ' + CONVERT(VARCHAR,@hs_formulary)
IF @searchString <> ''
SET @where_cond = @where_cond + ' AND (hs_hospital_name like ''%' + @searchString + '%'' OR hs_phone like ''%' + @searchString + '%'' OR hs_zip like ''%' + @searchString + '%'' OR hs_url like ''%' + @searchString + '%'')'
IF @hs_indication > 0
SET @where_cond = @where_cond + ' and (hs_indication in( ' + Convert(VARCHAR,@hs_indication) + ',' + Convert(VARCHAR,@h_hs) + '))'
IF @orderBy = 1
SET @orderByString = 'hs_hospital_name'
IF @orderBy = 2
SET @orderByString = 'status'
IF @desc_order = 1
SET @orderByString = @orderByString + ' DESC'
SET @sqlString = 'SELECT * from WY_CMP_HOSPITAL' + @where_cond + ' ORDER BY ' + @orderByString
EXEC(@sqlString)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO