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 |
|
asm
Posting Yak Master
140 Posts |
 Posted - 2012-03-03 : 00:28:28
|
| Hi, How can I pass variable in procedure as work as query ex--Create procedure spTransMaster (@Purpose smallint, @SP_Name varchar(50))ASBEGINDECLARE @mQuery Varchar(4000)SET @mQuery = 'TM.TransCode, TM.TransDate, TM.SellerCode, SM.Name as SellerName FROM TransactionMaster AS TM INNER JOIN SellerMaster AS SM ON TM.SellerCode = SM.Code' IF @Purpose=0 and @SP_Name='' BEGIN DECLARE @mCondition Varchar(2000) SET @mCondition =' Where TM.IsPurchase='1' Order by TM.TransDate' SELECT @mQuery +''+ @mConditionENDENDGOIn this example i want query body passed through variable because if where condition for procedure is varied then every time i have to write the whole query. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-03-03 : 09:41:19
|
| I did not quite get what you are trying from your description - but if my guess is correct, you are trying to send in a where clause as a parameter to the stored procedure. While you can certainly do this, it is very vulnerable to SQL injection attacks. So that may not be a good idea.If you can post two or three examples of the queries with the where clauses that you want to run, and describe how you want to make that generic, people on this forum may be able to suggest safer alternatives. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
hanmath pradeep
Starting Member
1 Post |
Posted - 2012-03-05 : 07:13:58
|
| hello sir,i have a doubt regarding inserting records into two tables and my doubt is that …….let us consider the following two tables,the first table is “pradeep” …SQL>select * from pradeep;SNO NAME PHNO— ——————– ———-1 pradeep 12 prasad 23 lavanya 34 tulasi 45 ravi 5my second table is … “prasad” …SQL> desc prasadName Null? Type—————————————– ——– —————————-SNO NUMBERNAME VARCHAR2(20)PHNO NUMBER(10)ADDRESS VARCHAR2(20)my condition and doubt is that ………….. in the above mentioned tables, the two columns i.e., SNO,NAME (COMMON COLUMNS IN BOTH TABLES),HERE I WOULD LIKE TO COPY THOSE TWO PARTICULAR COLUMN DATA FROM PRADEEP TO PRASAD WHERE I WOULD LIKE TO INSERT THE DATA INTO THE ANOTHER COLUMN I.E.,ADDRESS (COLUMN IN SECOND TABLE(PRASAD)) ………………THE BOTH OPERATIONS I.E., COPYING RECORDS FROM ONE TO ANOTHER TABLE AND INSERTING VALUES TO ONE TABLE HAS TO DONE SIMULTANEOUSLY WITH SINGLE QUERY …………..IS IT POSSIBLE IN PRACTICAL?AM WAITING HERE FOR YOUR REPLY ………………………..THANKS IN ADVANCEREGARDS,THANMATHPRADEEP@GMAIL.COMpradeep |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-03-05 : 07:16:03
|
quote:
Originally posted by hanmath pradeep
hello sir,i have a doubt regarding inserting records into two tables and my doubt is that …….let us consider the following two tables,the first table is “pradeep” …SQL>select * from pradeep;SNO NAME PHNO— ——————– ———-1 pradeep 12 prasad 23 lavanya 34 tulasi 45 ravi 5my second table is … “prasad” …SQL> desc prasadName Null? Type—————————————– ——– —————————-SNO NUMBERNAME VARCHAR2(20)PHNO NUMBER(10)ADDRESS VARCHAR2(20)my condition and doubt is that ………….. in the above mentioned tables, the two columns i.e., SNO,NAME (COMMON COLUMNS IN BOTH TABLES),HERE I WOULD LIKE TO COPY THOSE TWO PARTICULAR COLUMN DATA FROM PRADEEP TO PRASAD WHERE I WOULD LIKE TO INSERT THE DATA INTO THE ANOTHER COLUMN I.E.,ADDRESS (COLUMN IN SECOND TABLE(PRASAD)) ………………THE BOTH OPERATIONS I.E., COPYING RECORDS FROM ONE TO ANOTHER TABLE AND INSERTING VALUES TO ONE TABLE HAS TO DONE SIMULTANEOUSLY WITH SINGLE QUERY …………..IS IT POSSIBLE IN PRACTICAL?AM WAITING HERE FOR YOUR REPLY ………………………..THANKS IN ADVANCEREGARDS,THANMATHPRADEEP@GMAIL.COMpradeep
Please open your own thread 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-03-05 : 07:17:43
|
Aaaah and better post it in an ORACLE forum because here we go on MS SQL Server...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
|
|
Procedure