Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2012-05-31 : 11:30:09
|
| Hi,Due to business rules and requirements which can not be changed, there is a query as follows:SQL = select count(*) from objectNameI have found out that the sql inside the objectName which is a view is incorrect, so I have comeup with a separate sql that has the correct sql. The correct sql has table variables, insert and at the end select.Question:To use the new query, how can I use the same SQL as above but to make sure it uses the new sql.note that I still should have select count(*) from but the objectname can be changed if required.Can you assis please?Thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-31 : 11:43:41
|
quote:
To use the new query, how can I use the same SQL as above but to make sure it uses the new sql.
Whut? |
 |
|
|
HenryFulmer
Posting Yak Master
110 Posts |
Posted - 2012-05-31 : 11:43:42
|
| Can you post your current query? |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2012-05-31 : 12:07:19
|
| The c# code, is building a dynamic sql from a table as follows: select count(*) from field1 where field2 = 'GBP' field1 refers to the objectname which is a view and field2 refers to the field currency As explained before, I have a new sql which needs to be replaced with the name of the objectname Still not sure what to do? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-31 : 12:14:26
|
| you haveselect count(*) from objectnamebut want to change it to select count(*) from <sql statements return resultset>Is that correct?I think the only way would be an openrowset which would create a new connection so you probably wouldn't want to do that or maybe a table valued function.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2012-05-31 : 12:18:58
|
quote:
Originally posted by nigelrivett
you haveselect count(*) from objectnamebut want to change it to select count(*) from <sql statements return resultset>Is that correct?I think the only way would be an openrowset which would create a new connection so you probably wouldn't want to do that or maybe a table valued function.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
I would like to do:select count(*) from <sql statements return resultset> where currency = 'GBP'Can I use something like a view or a function that I can refer to by select count(*) from view or function or stored proc (which has my sql in it with table variables, insert) and then do where currency = 'GBP'?Thanks |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-05-31 : 19:27:00
|
| A View is limited to a single SELECT statement so anything more convoluted makes a View a non-starter. A table function could have more involved logic and would work syntactically like a Table, much like a View would. You seem committed to using a table variable and populating it before selecting the data out of it. Can you elaborate on why? Perhaps a better solution is available if we knew the what's and why's of your situation.=================================================There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-31 : 19:56:00
|
quote:
Originally posted by arkiboys
quote:
Originally posted by nigelrivett
you haveselect count(*) from objectnamebut want to change it to select count(*) from <sql statements return resultset>Is that correct?I think the only way would be an openrowset which would create a new connection so you probably wouldn't want to do that or maybe a table valued function.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
I would like to do:select count(*) from <sql statements return resultset> where currency = 'GBP'Can I use something like a view or a function that I can refer to by select count(*) from view or function or stored proc (which has my sql in it with table variables, insert) and then do where currency = 'GBP'?Thanks
you can have udf where you can alter the logic and then select data from it with where clause------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
select count(*) from objectname