Assigning result of Select

Question

Post reply

Assigning result of Select

gh.abtahi

SSC Enthusiast

Points: 185
More actions
December 16, 2010 at 3:18 am

#244476

Hi every one
Thank you for Great guideless .Dear sumit.agrawal I used your Suggestion and again I got pervious Results.
Dear CELKO Thank you for Wonderful Help and suggestions . I don't use any Mathematic Function Like Money .as I post my tables Code ,I use Money As a field that it is nvarchar value.
i am a beginner of SQL programming and absolutely I have a lot of problems.
i want to explain more about my Procedure.
When I Send parameters as null to my procedure i want to get the info from those tables (that I obvious the columns )
when I use null,null,Company-Name i want to get all info from those tables which is belonging to that company name.
when I use StartDate,null,Company-Name i want to get all info from those tables which is belonging to that company name and started from StartDate.
when I use null,EndDate,Company-Name I want to get all info from those tables which is belonging to that company name from first date of recording until EndDate.
when I use StartDate,Enddate,Company-Name i want to get all info from those tables which is belonging to that company name that these are between that two dates(StartDate and EndDate).
For Example :
when I sent this info ('89/01','89/04','آرد سنندج') i must get this info
monthYearMoneyTVMTVBTDOPTCMTCBTCRDTComTMon
فروردین89250000-------------2500001001389/01/05آرد سنندج750000
اردیبهشت8925000025000012345603/01/1389-----------آرد سنندج750000
خرداد89250000------------------آرد سنندج750000
تیر89250000------------------آرد سنندج750000
but when I execute that problem only I get 1 row and nothing more 🙁
and another problem can I Combine 2 last columns (Tcom and TMon).
What Must I do .It is urgent and I have a deadline.( I attach the Word that i wrote all sentences)
http://www.4shared.com/file/F7IqF0cb/Info.html
Thank you

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

gh.abtahi SSC Enthusiast Points: 185 More actions · Answer 1

HI CELKO

thank you for your Suggestions and helps.

1-Can you Help me about dummy Dates ? And How They work?:crying:

or do you have any reference for that?

2- I used your Code and I Got same results as past.as I copy it :

{----Code

declare @DateStart nvarchar(50)

declare @EndDate nvarchar(50)

declare @CompName nvarchar(50)

declare @Query nvarchar(4000)

set @Query ='Select [year].[month],

[year].[year],

[year].[money],

Tvarizi.[money] AS TVM,

Tvarizi.BillNum AS TVB,

Tvarizi.DateOfPayBill AS TDOPB,

Tcash.[Money] AS TCM,

Tcash.BillNum AS TCB,

Tcash.Recievedate AS TCRD,

case isnull(Tvarizi.WCashID,0) when 0 then TCashTCompany.CompanyName else TvariziTCompany.CompanyName end AS TCom,

case isnull(Tvarizi.WCashID,0) when 0 then TCashMoney.TotallValue else TvariziMoney.TotallValue end AS TMon

From [year]

Left outer join TVarizi On substring( TVarizi.DateOfPayBill,3,5)=[year].Date

Left outer join TCash On substring( TCash.Recievedate,3,5) = [year].Date

Left outer join TCompany as TCashTCompany On (TCashTCompany.CompanyName = TCash.companyName)

Left outer join TCompany as TvariziTCompany On (TvariziTCompany.CompanyName = Tvarizi.companyName)

Left outer join [Money] as TCashMoney On (TCashTCompany.CompanyName = TCashMoney.companyName)

Left outer join [Money] as TvariziMoney On (TvariziTCompany.CompanyName = TvariziMoney.companyName)

Where 1=1 '

if isnull(@CompName,'')<>''

set @Query = @Query + ' and (case isnull(Tvarizi.WCashID,0) when 0 then TCashTCompany.CompanyName else TvariziTCompany.CompanyName end ='''+@CompName+''')'

if isnull(@DateStart,'')<>'' and isnull(@EndDate,'')<>''

set @Query = @Query + ' and ([Year].Date BETWEEN COALESCE (''89/01'', CAST (''0001-01-01'' AS DATE)) AND COALESCE (''89/04'', DATEADD(DAY, 1, CURRENT_TIMESTAMP)))'

if isnull(@DateStart,'')<>''

set @Query = @Query + ' and ([Year].Date >='''+@DateStart+''')'

if isnull(@EndDate,'')<>''

set @Query = @Query + ' and ([Year].Date <='''+@EndDate+''')'

set @Query = @Query + 'order by TCom, [year].[year], [year].[month]'

print @Query

exec (@Query)

-----Code}

I use your code correctly?

Thank you

itdevnews SSC Enthusiast Points: 115 More actions · Answer 2

From first looks I would try to avoid dynamic SQL and the where 1=1 looks inappropriate.

itdevnews SSC Enthusiast Points: 115 More actions · Answer 3

@celko. I would keep the null dates as nulls and exclude them from the query.

Then use a WITH or a subquery to filter out the dates from the resulting query.

gh.abtahi SSC Enthusiast Points: 185 More actions · Answer 4

hi

@itdevnews ,So Dear How I can Change my code and get my result??????????????