|
But if I declare it in the @Command string, I cannot use @voter_id outside the string, correct?
I still get the same error message because @voter_id only exists in the string.
Everything makes sense in someone's mind
|
|
|
|
|
Absolutely.
Dasblinkenlight's solution is a nice one to the problem, BTW.
I normally build a temp table and just run the data into that. Using an insert or an update.
I know it seems (both suggested solutions) like a lot of overhead to get one piece of data out but that's really what it takes.
create table #Value (
val int
)
set @command = 'insert into #value (val) SELECT top 1 voterid FROM tblCamp_CT WHERE ' +@Query
exec(@command)
set @voter_id=(select top 1 val from #Value)
delete #Value
Like that.
You could also research sp_executesql for your project.
this works, for example:
declare @voter_id int , @query varchar(max), @command nvarchar(4000)
,@parm nvarchar(20)
set @query='1=1'
SET @Command = 'SELECT top 1 @voter_id = voterid FROM tblCamp_CT WHERE ' + @Query
set @parm ='@voter_id int output '
exec sp_executesql @command,@parm ,@voter_id out
select @voter_id test
As you can see the sp_executesql gives you another option.
Some of this depends on what your personal flavor is toward a given solution.
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
Blue Boy is correct.
The 'why' of it is that the context has shifted. The exec runs inside of it's own little world that has no idea of what occurred outside of the @command string.
You can see a similar effect whenever you hit a 'go' statement. Variables become undeclared because the context has changed. The difference is that when you run an exec it doesn't break the context outside of the exec the way 'go' does.
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
Others have answered the 'why' question, so let's go straight to the 'how' part.
As clever as it may seem, your trick does not work, because SQL server does not let you parameterize dynamic SQL. However, you can trick it by observing that dynamic SQL is essentially an anonymous stored procedure; once you give it a name, you can start passing parameters to it.
|
|
|
|
|
That would be another reason not to use stored procedures. Things like that are so much simpler when the SQL is in your DAL.
|
|
|
|
|
PIEBALDconsult wrote: That would be another reason not to use stored procedures
Boo, hissss, brrrppp and other sundry rude noises. Oh wait we've been round this tree before.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
PIEBALDconsult wrote:
That would be another reason not to use
stored procedures. Things like that are so much simpler when the SQL is in your
DAL.
I'll bite, this is easy in a proc. Seems like it would be way more difficult embedded in the DAL. I'm interested in how you would accomplish it?
EXEC sp_executesql @DYNA_SQL, N'@NUM_ROWS int out'
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
What does it do? 
|
|
|
|
|
Pretty much the same thing that smcnulty2000 suggested doing in the second suggestion. sp_executesql allows for parameters to be fed in / out the dynamic SQL being executed.
http://msdn.microsoft.com/en-us/library/ms188001.aspx[^]
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Here are a couple of simple examples. Not using my usual data access classes.
I prefer the ExecuteScalar, it was designed for this sort of thing.
System.Data.IDbConnection dbc = new System.Data.SqlClient.SqlConnection
(
@"Server='localhost\SQLEXPRESS'; Database='Rubbish'; Trusted_Connection='True'"
) ;
System.Data.IDbCommand cmd = dbc.CreateCommand() ;
cmd.CommandText = "SELECT @voter_id=Id FROM Account WHERE " + "Name = 'Two'" ;
System.Data.IDbDataParameter prm = cmd.CreateParameter() ;
prm.ParameterName = "@voter_id" ;
prm.DbType = System.Data.DbType.Int32 ;
prm.Direction = System.Data.ParameterDirection.Output ;
cmd.Parameters.Add ( prm ) ;
dbc.Open() ;
cmd.ExecuteNonQuery() ;
int id = (int) prm.Value ;
cmd.Parameters.Clear() ;
cmd.CommandText = "SELECT Id FROM Account WHERE " + "Name = 'Three'" ;
object o = cmd.ExecuteScalar() ;
if ( o != System.DBNull.Value )
{
id = (int) o ;
}
dbc.Close() ;
|
|
|
|
|
Interesting, those are not very far off from I have used in code. Though most of my work is all database level work now (data warehousing, stored procs, SSIS, SSAS and SSRS), so I've come to use stored procs for everything. Just seems easier to me.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
S Douglas wrote: I've come to use stored procs for everything
They are rarely the right tool for the job.
|
|
|
|
|
PIEBALDconsult wrote: They are rarely the right tool for the job.
No other tool suits the needs,  . I don't disagree with you sediments. However, in my world its all that exists.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Here's another take on it:
public static T
GetAccountId<T>
(
this System.Data.IDbConnection dbc
,
string Filter
,
params System.Tuple<string,object>[] Parameters
)
{
T result = default(T) ;
System.Data.IDbCommand cmd = dbc.CreateCommand() ;
cmd.CommandText = "SELECT Id FROM Account WHERE " + Filter ;
if ( Parameters != null )
{
foreach ( System.Tuple<string,object> p in Parameters )
{
System.Data.IDbDataParameter prm = cmd.CreateParameter() ;
prm.ParameterName = p.Item1 ;
prm.Value = p.Item2 ;
cmd.Parameters.Add ( prm ) ;
}
}
dbc.Open() ;
object o = cmd.ExecuteScalar() ;
if ( o != System.DBNull.Value )
{
result = (T) o ;
}
dbc.Close() ;
return ( result ) ;
}
|
|
|
|
|
Dear All,
I have one table with column called testId. This Id is referenced in six other table columns.
Its not like a primary /foreign key type but can be used to compare values using inner join.
There is a requirement to changed all of this testId(10 digits code originally) to new set of TestId(11 digits code now).
I could have done ON UPDATE CASCADE to change all testid's in other six tables if there was primary /foreign key defined.
Is there any one who can give me a hint on the script to update on the master table so that all the other tables will contain the relevant changes on the testId.
For example : Old value New Value
testId = s401 I00010
s402 I00020
s403 I00030
s403 I00040
This is going be applied to over 30k rows hence i just needed a script to do it.
Thank you so much for your time.
<div class="modified">modified on Wednesday, July 13, 2011 5:37 PM</div>
|
|
|
|
|
What did you do so far to archive your query? Can you show us your query?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
It is always a good idea to have foreign key relationships on tables. Creating indexes on the foreign keys will also speed up queries.
Why don't you try creating the foreign key relationships now with the ON UPDATE CASCADE option?
|
|
|
|
|
Since they aren't linked using a FK/PK relationship I'm not sure why you would have to change them in the same query. If s401 is always equal to I00010 then just write a mapping script and run it against all seven tables.
If you are really worried then you could add a field where the updated values appear, leaving the old values in place, and then write another update that takes the new value, puts it in place of the old value and drop the updated value column. That way you can stop before the final step and check your work.
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
Hi, I am providing a solution to this problem but ideally you should follow as what Shameel has said..
Let us create 4 tables (one master and three child tables) as under
CREATE TABLE [dbo].[tblmaster](
[testID] [varchar](50) NULL,
[Name] [varchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblChild1](
[testID] [varchar](50) NULL,
[Name] [varchar](50) NULL,
[Age] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblChild2](
[testID] [varchar](50) NULL,
[Name] [varchar](50) NULL,
[Address] [varchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblChild3](
[testID] [varchar](50) NULL,
[Sex] [varchar](6) NULL,
[Address] [varchar](50) NULL,
[Phone Number] [varchar](50) NULL
) ON [PRIMARY]
insert into tblmaster
select 's401', 'name10' union all
select 's402', 'name11' union all
select 's403', 'name12' union all
select 's404', 'name14'
insert into tblChild1
select 's401', 'name20', 20 union all
select 's402', 'name21', 21 union all
select 's403', 'name22', 22 union all
select 's404', 'name23',23
insert into tblChild2
select 's401', 'name30', 'address30' union all
select 's402', 'name31', 'address31' union all
select 's403', 'name32', 'address32' union all
select 's404', 'name33', 'address33'
insert into tblChild3
select 's401', 'male', 'address40','9885446789' union all
select 's402', 'female', 'address41','12345678' union all
select 's403', 'male', 'address42','34567891' union all
select 's404', 'female', 'address43','0986234'
Select * from tblmaster
Select * from tblChild1
Select * from tblChild2
Select * from tblChild3
Now fire the below query
exec sp_msforeachtable N'
IF EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID(''?'') AND name = ''testID'')
EXEC (''UPDATE ? SET testID= case when testID = ''''s401'''' then ''''I00010''''
when testID = ''''s402'''' then ''''I00020''''
when testID = ''''s403'''' then ''''I00030''''
when testID = ''''s404'''' then ''''I00040''''
end'')'
N.B.~
Use this example as a reference for your problem.
Thanks
Niladri Biswas
modified on Tuesday, July 19, 2011 5:48 AM
|
|
|
|
|
Hi all,
I have table and inside table there are two columns named Amount and Balance i want to calculate the balanace as Professive like below
Balance 6000
Amount Balance
1500 4500
2000 2500
1000 1500
500 1000
Initially iam getting the balance from database table than have to perform the Progressive calculation .
May i have to use COALESCE function ?? Or how i can calculate the ProgressiveBalance like above any hint.
Any Idea ?
Best Regards
Rameez
|
|
|
|
|
|
One way to do this is with a subquery.
I assume an element number which is an int, that is numbered according to how you want to display this. However this is more sensible if you actually have dates and do the subquery comparison based on the date. Element also could be your customer order id (or invoice id, or whatever) if those are sequential.
SELECT [Element]
,[Amount]
,(select SUM(Amount) from AmountTabletb where tb.Element<=ta.Element )
NewBalance
FROM [yourdatabase].[dbo].[AmountTable] ta
And then adding in the original balance with another subquery.
SELECT [Element]
,[Amount]
,(select SUM(Amount) from AmountTabletb where tb.Element <= ta.Element )
+ (select top 1 balance from AmountTable tb where tb.Element=1 ) NewBalance
FROM [yourdatabase].[dbo].[AmountTable] ta
Remember; your subquery can return only a single value per row or you get an error so always use a 'top 1' clause or a summary function like SUM() or Max().
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
Thanks for reply
Actually i have the below Table with multiple columns
Declare @Table Table
(
EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
Select Abc,07/03/2010,6000,0 UNION ALL
Select xyz, 07/03/2011,1500,0 UNION ALL
Select Rameez, 07/03/2011,2000,0 UNION All
Select Abhijit, 07/03/2011,1000,0 UNION ALL
I have the above table and have to calculate the progessive Balance for each row is it possible using subquery????
Regards
|
|
|
|
|
Yes. (BTW, this is not really a beginner's task you are attempting in case you are wondering.)
Yes, but life gets easier if you have a row number of some kind.
As your example shows there's no particular order to these entries; they all have the same date.
There is nothing indicating which ones you want first. I've added single quotes to your example entries.
If you added another column to indicate order you could have this query:
Declare @Table Table
(
row int, EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
Select 1, 'Abc','07/03/2010',6000,0 UNION ALL
Select 2, 'xyz', '07/03/2011',1500,0 UNION ALL
Select 3, 'Rameez', '07/03/2011',2000,0 UNION All
Select 4, 'Abhijit', '07/03/2011',1000,0; --UNION ALL
select xx.*
,(select sum(Amount) from @Table xy where xy.row <=xx.row ) RunningTotal
from @Table xx
As you can see the number inserted with the information is working just fine.
This next version uses an identity column in the table to do the same job so if you have a lot of entries you won't have to pre-set all of their numbers.
Declare @Table Table
(
row int identity(1,1), EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
(
EmployeeName
,joiningDate
,Amount
,Balance
)
Select 'Abc','07/03/2010',6000,0 UNION ALL
Select 'xyz', '07/03/2011',1500,0 UNION ALL
Select 'Rameez', '07/03/2011',2000,0 UNION All
Select 'Abhijit', '07/03/2011',1000,0; --UNION ALL
select xx.*
,(select sum(Amount) from @Table xy where xy.row <=xx.row ) RunningTotal
from @Table xx
In this last example you can see I'm using row_number() to artificially create a row number on the fly.
Declare @Table Table
(
EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
Select 'Abc','07/03/2010',6000,0 UNION ALL
Select 'xyz', '07/03/2011',1500,0 UNION ALL
Select 'Rameez', '07/03/2011',2000,0 UNION All
Select 'Abhijit', '07/03/2011',1000,0;
with CTE01 as
(select *
,ROW_NUMBER() over (order by joiningdate) row
from @Table )
select xx.*
,(select sum(Amount) from CTE01 xy where xy.row <= xx.row ) RunningTotal
from CTE01 xx
I went ahead and used a common table expression (CTE) here because row_number can be a little intransigent when you use it directly in the subquery, but it works just fine in the CTE.
Hopefully this all makes enough sense that you can compare the examples to what you are trying to do and make something out of this.
You should be able to paste each of the above into it's own window in management studio and you can see how they work better. Don't put them in the same window, of course, but I'm guessing you know that one.
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
I'm currently building a web-based store for my company that allows students register for different classes. In the administration section of the website there is page where a user can create a new class for students to register for. On this page there is multiple items that the person must fill out; date and time of class, the instructor, the course name, etc. Some of these items are stored in a database. For example, there is a table that has all the instructor's names and ID numbers, and there is another table with all the course names, price, etc.
My problem is that I want to populate several drop-down menus with information from different tables that have no relationship. For example I want to pull all the instructor's names (to be placed in one drop-down) and also select all the different course names (to be placed in a different drop-down). Now I could use multiple SQL statements to do this, but I was hoping there was a way to do it with a single SQL statement, however it cannot use JOINS. This is because, in the above example, the instructors table and the course table do not have any columns that are the same that the JOIN can be done on. I wanted to use a single SQL statement since this is web-based and the less calls to the database the quicker the website will load.
Any ideas or suggestions would be greatly appreciated, and thanks in advance for any help.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
