|
How to create a stored procedure in MySql using:
1.create query
2.insert query
|
|
|
|
|
Have you tried reading the documentation[^]?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi all,
declare @str1 varchar(500)
declare @str2 varchar(500)
declare @str3 varchar(500)
declare @str4 varchar(500)
select @str1=ud_id from cm_tb_002 where username='admin';
select @str3=role_id from cm_tb_012 where tb012_id=@str1;
select @str4=tb001_id from cm_tb_001 where menu_activ = 1 and child_lvl = 0 and [15]='1';
select tb001_id from cm_tb_001 where menu_prnt=@str4;
Here i want to pass @str3 to a this query
select @str4=tb001_id from cm_tb_001 where menu_activ = 1 and child_lvl = 0 and @str3='1';
but the @str3 column is an bigint column. ex. 15 i.e column name
In sql sever this value is storing as [15] as column name. Now i want to
pass this value to this query
select @str4=tb001_id from cm_tb_001 where menu_activ = 1 and child_lvl = 0 and @str3='1';
Please help me on this.
i want to join this group
|
|
|
|
|
You would need to construct your SQL statement as a string and execute it using the EXEC statement.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hai all
In my table i have some records with serial number
ex:
1
2
3
4
5.
if i delete 3 means 4 should be decremented as 3 and 5 should be decremented as 4.
Plse give me a logic to do this.
Thanks in advance.
With regrds
Lijo Rajan
|
|
|
|
|
Lijo Rajan wrote: Plse give me a logic to do this.
What have you tried? Show that you have made a serious attempt to solve the problem and I'll help you.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
I dont know the logic.I am requesting u to give me logic or sql statement to do this
|
|
|
|
|
Lijo Rajan wrote: I dont know the logic.I am requesting u to give me logic or sql statement to do this
I don't give code to people, especially when they haven't demonstrated that they have tried to solve the problem themselves.
I'll give you a hint though - use an UPDATE query to decrement all serial no. values greater than the one being deleted. Your biog states that you are a professional software engineer, so you should be able to figure out how to implement this.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
UPDATE IPMS_TBL.VDR_WO_LINE_ITEM SET serialnumber=serialnumber-1
WHERE WO_ID=@WOId AND WO_LINE_ITEM_ID>@WOLineItemId
@WOLineItemId=id which i deleted
|
|
|
|
|
I assume that you mean that you have an identity column that you want to reorder on a delete. You should know that this is a dangerous thing to attempt. For a start, suppose that you have 10,000,000 records in a table and you delete record number 3. That leaves 9,999,996 records being updated just to keep this in sync. More importantly, if you have this fields as a PK and there are foreign key relationships then you also need to reorder all of those foreign keys. Please rethink this strategy before you attempt to go any further.
|
|
|
|
|
If you really need the records to be sequentially numbered in the output of a query, and you're using SQL Server 2005, consider sorting the output using ORDER BY and using the RANK function to get the sequential numbering.
DoEvents : Generating unexpected recursion since 1991
|
|
|
|
|
there is 2 table named table 1 and table 2 in the db
where both table have a comman coloumn named employee_code.
let table1 conatain emplyee_code-1,2,6,8,9
and table2 contain employye_coce-2,4,6,7,8
now i want a sql statement which will give me the out put as follows
i want to check(let say employee code1) wather a particular employee_code
present in table 1 and not present in table2 .if it run succesfully it should give massage emplyee code exists else
doesn't exists.
means if i select emp_code 1 then the message would be "emplyee code exists"
i select emp_code 2 then the message would be "emplyee code doesnt exists"
|
|
|
|
|
Use the EXISTS[^] operator to check for the existence of rows with a particular employee code in table 2.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi,
Master Table
SMID(int) Descr(varchar)
43 A
47 B
208 C
502 D
549 E
Child Table
Descr_SMID(varchar)
208
502,549,43,47
502,549
Now I want result like this using count.
A (1)
B (1)
C (1)
D (2)
E (2)
kiran banker
|
|
|
|
|
I don't follow the table layout, but it looks like you need something like
select sum(count), letter from tbl group by letter
where letter is the column that gives you A/B/C and count is the column you want to combine. use count instead of sum if you're counting columns and not combining their values
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
banker_kiran wrote: Child Table
Descr_SMID(varchar)
208
502,549,43,47
502,549
Your child table violates one of the principles of 1st normal form. Values for each column-row intersection should be atomic. You should consider splitting these values out, otherwise it will be very difficult to query the data efficiently.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
pmarfleet wrote: Your child table violates one of the principles of 1st normal form
Yes, it certainly does.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
|
|
|
|
|
Hi all,
I have a dataset which has 3 tables. They have relation by ds.Relations.Add(...)
Now I want a filteration like a master table i.e. ds.tables(0) should be filtered by one column like ds.tables(0).Select("Site_ID = 5")
(which will return datarow array now) and I want its children rows should be filtered appropriately.
Instead of table.Select(...) is there any other way?
Thank You
|
|
|
|
|
You could create a DataView-object for the DataTable via DataView dv = new DataView(ds.Tables[0]); . Then set the RowFilter with this line
dv.RowFilter = "Site_ID = 5";
|
|
|
|
|
i creating function
when used execute statment in function i have error:
Invalid use of 'EXECUTE' within a function.
-- modified at 3:43 Saturday 1st December, 2007
|
|
|
|
|
Rami Said Abd Alhalim wrote: Invalid use of 'EXECUTE' within a function.
Sounds like you need to use the execute command correctly.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi all,
Here iam getting RoleId and iam storing it in a variable @str2.Now
i want to use this variable data as a column name in third query i.e
@str3.Please help me on this.
alter procedure LogProc
(
@UserId varchar(500),
@Password varchar(500)
)
as
Begin
declare @str1 varchar(500)
declare @str2 varchar(500)
declare @str3 varchar(500)
select @str1=AstId from UserTable where UserId=@UserId and Password = @Password;
select @str2=RoleID from AstProf where AstId =@str1 ;
select @str3=MenuId from MenuTable where @str2='y';
select @str3;
End
exec LogProc 'veeresh','veeresh'
i want to join this group
|
|
|
|
|
you cannot pass the Column dynamically unless using Dynamic SQL.
Now the question is that how to use dynamic sql
declare a variable assign it TSQL with any dynamic values
execute it .
________________
Tehmina Qureshi
****************
|
|
|
|
|
Hi Sir,
Iam new to sql.Can you tel me how to use dynamic sql in my query.
Veeresh
i want to join this group
|
|
|
|
|
you may declare and use dynamic sql like this;
CREATE PROCEDURE GetRecords ( @DynamicColumnName varchar(100) ) AS
DECLARE @dynamicsql varchar(255)
SELECT @dynamicsql = 'SELECT Column1, Column2, Column3 ' +
'FROM TableName ORDER BY ' + @DynamicColumnName
EXEC(@dynamicsql)
Hope this will help.
________________
Tehmina Qureshi
****************
|
|
|
|