Hi Irene,
Your select string is 246 characters long which requires double size (492) to store as nvarchar. It shouldn't be and it isn't trancated when I run your code with nvarchar(500). Btw, the nvarchar variable can be up to 4000 characters.
Anyway, you can get desired result w/o dynamic sql.
select cl_code, cl_init, cl_fname, cl_lname, cl_cltype, cl_language, cs_telno, cs_smrecno, cs_freq' +
from bkclsmscl left join bkclporg on cs_clrecno = cl_recno
where (cl_celno <> 'DELETE' or cl_action is null)
and LEN(cl_celno) = 10
AND (@cs_smrecno NOT BETWEEN 1 AND 7 OR cl_smsi = 1)
>I need to build up an select statement depending on the parameters being passed through. The total size exceeds 256 charcaters and therefor truncating the variable. The variable is defined as @statement nvarchar(256). I tried to use nvarchar(500), but nothing changes.
>
>Code follows:
>
>CREATE PROCEDURE select_sms_clients
> @cs_smrecno integer,
> @cl_cltype varchar(20),
> @cl_language varchar(10)
>
>AS
>declare @statement nvarchar(256)
> @statement2 nvarchar(256)
>
>
> if @cs_smrecno between 1 and 7
> set @statement = N'select cl_code, cl_init, cl_fname, cl_lname, cl_cltype, cl_language, cs_telno, cs_smrecno, cs_freq from bkclsmscl left join bkclporg on cs_clrecno = cl_recno where (cl_celno != ''DELETE'' or cl_action is null) and LEN(cl_celno) = 10 and cl_smsi = 1'
> else
> set @statement = N' select cl_code, cl_init, cl_fname, cl_lname, cl_cltype, cl_language, cs_telno, cs_smrecno, cs_freq from bkclsmscl left join bkclporg on cs_clrecno = cl_recno where (cl_celno != ''DELETE'' or cl_action is null) and LEN(cl_celno) = 10'
>
> select @statement
>
> if @cs_smrecno is not null and @cs_smrecno != ''
> begin
> if @cs_smrecno = 7
> set @statement2 = rtrim(@statement2) + N' and cs_smrecno = ' + str(@cs_smrecno) + N' and cl_cltype = ''DEBTOR'''
> else
> set @statement2 = rtrim(@statement2) + N' and cs_smrecno = ' + str(@cs_smrecno)
> end
>
>
>Thanks
--sb--