|
Pranav Thakur wrote: Select @variable=@vr from test
This line holds the problem. You wil need to create a dynamic query to display values.
|
|
|
|
|
hi...
Try This one....
Declare @loopId int
set @loopId=1
Declare @vr varchar(100)
declare @variable varchar(100)
Declare @qry VarChar(1000)
while @loopId<=4
begin
set @vr='Q'+''+Convert(varchar,@loopId)+''
Select @variable=@vr from test
print(@variable )
Set @qry = (Select @variable=@vr from test)
set @loopId=@loopId+1
Exec(@qry)
end
Take Care ....
sure there will not be any prob now if still having prob..then
reply..
take care...
By
|
|
|
|
|
hi all
i have develping an application in vb.net with mssql express 2005,
now i want to fetch records from a table with different conditions
i want to use a parameter as a column name in which i want to pass differnt field names and an other parametr to pass condition value.
here is the Sp by which i trying to fecth the data but could not....
pl help me
CREATE PROCEDURE TEMP1EMPINFO
--CREATE PROCEDURE TEMP1EMPINF
(
@Tbl_col_name varchar(100),
@Col_val varchar(100)
)
AS
Begin
Select * from table where @tbl_col_name= @col_val
end
GO
thanks
rmshah
Developer
|
|
|
|
|
You will have to build a SELECT statement dynamically and then execute it using sp_executesql[^]. But as far as I know it's not very good performancewise. Google for "dynamic SQL" and read about it.
How many different values of @Tbl_col_name will you be using?
Wouldn't it be easier to create a separate SP for each case?
|
|
|
|
|
Sure you can try this One...
Declare @qry VarChar(1000),
@clmname VarChar(100),
@tabname VarChar(100)
Set @qry = (Select @clmname from @tabname)
Exec(@qry)
hAVE A nICE DAY....
bY...
|
|
|
|
|
Hello sir,
I have a table (TreeMaster) with fields (TreeId, MemberName, ProductId) and I have 1 to 100 records.
Select x.TreeId, x.MemberName, x.ProductId
From TreeMaster x, TreeMaster y
Where x.ProductId = y.TreeId
The above result between 1 to 100 records.
But I want between 10 to 100 records only.
I don't know how to change the above query.
Logu
|
|
|
|
|
I'm assuming you meant TreeID between 10 and 100..
Select x.TreeId, x.MemberName, x.ProductId
From TreeMaster x, TreeMaster y
Where y.TreeId >= 10 and y.TreeId <= 100 and x.ProductId = y.TreeId
|
|
|
|
|
Thank you, got the answer.
Logu
|
|
|
|
|
What database do you use?
|
|
|
|
|
Good Day,
When using stored procedures with multiple statements, how do you tell your stored procedure to rollback any changes when the stored procedure did not successfully executed all statements?
Thanks!
It is said that the most complex structures built by mankind are software systems. This is not generally appreciated because most people cannot see them. Maybe that's a good thing because if we saw them as buildings, we'd deem many of them unsafe.
|
|
|
|
|
do a begin tran, execite your code and after each piece check @@error - if its <> 0 rollback tran and return. At the end commit tran
create proc p1
as
DECLARE @SqlErrorNum int,
@SqlErrorText varchar(255),
@Process varchar (64)
select @Process = object_name(@@procid)
begin tran
update table ....
select @SqlErrorNum = @@ERROR
IF @SqlErrorNum != 0
BEGIN
select @SqlErrorText = 'updating table'
goto ErrorHandler
END
update table2 ....
select @SqlErrorNum = @@ERROR
IF @SqlErrorNum != 0
BEGIN
select @SqlErrorText = 'updating table2'
goto ErrorHandler
END
commit tran
return 0
ErrorHandler:
SELECT @SqlErrorText = @Process + 'ERROR ' + @SqlErrorText
INSERT INTO T_In_thinkFolioOvernightLog (processName,note) values (@Process,@SqlErrorText)
RAISERROR @SqlErrorNum @SqlErrorText
RETURN 1
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks, solved my problem.
It is said that the most complex structures built by mankind are software systems. This is not generally appreciated because most people cannot see them. Maybe that's a good thing because if we saw them as buildings, we'd deem many of them unsafe.
|
|
|
|
|
i made simple program to insert data in table,it shows the problem that operation must be updateable query.it works well on
local computer but i load on Internet website it shows error that it must be updateable query.
My Code is as follows:
OleDbConnection conn;
OleDbDataAdapter dataAdapter;
DataSet ds;
string cs;
conn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source="+
Server.MapPath("db1.mdb"));
conn.Open();
cs="INSERT INTO trader(name)values(@firstname)";
OleDbCommand dc=new OleDbCommand(cs,conn);
OleDbParameter fparam=new OleDbParameter("@firstname",OleDbType.VarChar,10);
fparam.Value=TextBox1.Text;
dc.Parameters.Add(fparam);
dc.ExecuteNonQuery();
conn.Close();
|
|
|
|
|
I would guess that the Access database is Read Only when it is put on the website.
|
|
|
|
|
SQL novice here..
Hi, I'm having trouble formulating an SQL query and tbh I'm not even sure if its possible to do what I'm trying to do [without a cursor].
I have two tables with a standard 1->M relationship. If a certain field in ALL the linked records are set to a certain value, I need to update a field in the parent record, if the field is set to a different value in one of the linked records, i do nothing. How should I phrase the query?
Edit for clarification: Lets say it is a Client table, with a related Tasks table, and the intermediate ClientTasks table. If all the Tasks related to a Client are marked as done, I need to update a field in the Client's record to mark the client as having no outstanding tasks.
Thanks in advance..
modified on Tuesday, June 17, 2008 4:26 AM
|
|
|
|
|
can u clarify Your question
regards
Joe
|
|
|
|
|
|
hi
from your clarification i understand that you have to update a master table an a child table...you can handle this either in Stored procedure or in Code itself
Regards
Joe
|
|
|
|
|
There are several ways, but the quickest (assuming decent indexing) should be along the lines of
update client
set alldone = 'Y' -- signifies no outstanding tasks
from client c
where not exists (select 1 from clienttasks t where t.clientid = c.clientid and t.alldone = 'n')
This may not mathc your tables, but you should get the general idea I hope.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
select IntValue+VarcharValue from tbl
but it's not possible so how to convert IntValue in varchar in query only like
select convert.tostring(IntValue)+VarcharValue from tbl
what can I do despite of convert.toString because convert.Tostring wrong
thanks
You get the best out of others when you give the best of yourself.
|
|
|
|
|
hi
You can use Convert function for this.
like
select convert(varchar,Intvalue)+varcharvalue from table.
Regards
Joe
|
|
|
|
|
Use Convert or Cast functions
SELECT Convert(VarChar(10), IntValue) + VarcharValue .....
SELECT Cast(IntValue, VarChar(10)) + VarcharValue .....
in the example I've used 10 characters i.e. max 10 degit number
Regards
KP
|
|
|
|
|
Try using SQL Books Online rather than putting .NET code into sql statements and expecting them to work.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Generally I return just the data from SQL Server and perform any formatting necessary in the 'client' code (i.e. the code that called into SQL Server).
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Hi...
Try This One Will Work
Select Convert(varChar(20), Role) + LogInID From LogIn
By
Take Care..
|
|
|
|