|
let me tell you the rwquirement of my query that i have wriiten ...so that u could tell me whether my query is right or wrong according to the requirement..
actually i have two tables sc_bill and sc_bill_details...and i have requirement to get the dates available in sc_bill table ( from july 2015 to till date) to sc_bill_details...is my approch is correct to get the desired output.? i want only query
Thanks
|
|
|
|
|
Read the entire thread to date.
If you run JUST the select:
Member 11919722 wrote: select dt_bill_date from sc_bill where sc_bill.st_bill_no=sc_bill_details.st_bill_no and dt_bill_date>='1-7-2015'
what values do you get? Do you get a record with a NULL date?
|
|
|
|
|
You'll need an inner join to the sc_bill_details table to run the query without the outer UPDATE statement.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Correct... missed that; thanks for catching it.
So... reword the update to be a query with an inner join.
|
|
|
|
|
the thing is that if m writing same query with two defferent tables but not with sc_bill and sc_bill_details....m getting the required output but not with tables that i should get.
|
|
|
|
|
i did as u said n got output also without null values but if m writing the same query...with update statement as outer query the inner query gives foloowing error which is obvious coz inner query returns more than single value ..the query will be as follow as u said right?
update sc_bill_details set dt_open_date=(select dt_bill_date from sc_bill,sc_bill_details where sc_bill.st_bill_no=sc_bill_details.st_bill_no and dt_bill_date>'2015-07-01' )
getting follwing error when wrote above query ....
more than one row returned by a subquery used as an expression
i cant even use in operator with update statement since that will lead to syntax error??
|
|
|
|
|
m not getting any null value if m writing inner query ...
|
|
|
|
|
Hi All,
I want to migrate database from Mysql Server to Sql Server.I migrated database tables structure and Stored Procedures successfully and some database records also by using Sql Server Migration Tool for Mysql.But all the table records not move.
So what I do please give me guidance for this.
|
|
|
|
|
ALTER PROCEDURE [dbo].[SP_SearchByUserNeed]
(
@ColumnName varchar(20),
@Value Varchar(100)
)
AS
BEGIN
BEGIN try
Select * From Product where @ColumnName like @Value+'%';
END try
BEGIN catch
print('Error in [SP_SearchByProductName]')
END catch
END
|
|
|
|
|
You cannot use a variable in this manner. You would need to use dynamic code.
Mongo: Mongo only pawn... in game of life.
|
|
|
|
|
Hay, Thank you so much for your valuable response. I am agree with you. But i can't find a way to do the replacement of the column name dynamically with a variable. If possible please explain the same. I believe. Thank you
|
|
|
|
|
Try something like this - as I am on holiday I have not had a chance to test this:
ALTER PROCEDURE [dbo].[SP_SearchByUserNeed]
(
@ColumnName varchar(20),
@Value Varchar(100)
)
AS
BEGIN
BEGIN try
declare @qry nvarchar(max);
set @qry = 'Select * From Product where @ColumnName like ' + '''' + @Value + '%' + '''';
exec(@qry);
END try
BEGIN catch
print('Error in [SP_SearchByProductName]')
END catch
END
Hugs and kisses to the downvoter ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 16-Sep-15 8:55am.
|
|
|
|
|
That code is vulnerable to SQL injection. To execute dynamic parameterized queries within SQL, you need to use sp_executesql[^].
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
To create dynamic T-SQL you start with a variable for the code
DECLARE @strSQL NVARCHAR(1000);
SET @strSQL = N'Select * From Product where ' + @ColumnName + ' like @Value+''%'';';
EXECUTE sp_executesql @strSQL, @Value
Hope this helps
Mongo: Mongo only pawn... in game of life.
|
|
|
|
|
Almost! You're missing the parameter definition from sql_executesql. I'd also be inclined to validate the column name before concatenating it.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
It's early here. 
Mongo: Mongo only pawn... in game of life.
|
|
|
|
|
This should work:
ALTER PROCEDURE dbo.SP_SearchByUserNeed
(
@ColumnName varchar(20),
@Value varchar(100)
)
AS
BEGIN
DECLARE @RealColumnName sysname;
DECLARE @Statement nvarchar(max);
SELECT
@RealColumnName = name
FROM
sys.columns
WHERE
object_id = OBJECT_ID('Product')
And
name = @ColumnName
;
If @RealColumnName Is Null
BEGIN
RAISERROR('Unknown column: "%s"', 16, 1, @ColumnName);
Return;
END;
SET @Statement = N'SELECT * FROM Product WHERE ' + QuoteName(@RealColumnName) + N' Like @Value + ''%''';
EXEC sp_executesql @Statement, N'@Value varchar(100)', @Value;
END
This will validate that the column name passed in is a valid column in the Product table, and avoid SQL Injection[^] in the dynamic query.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi
I want to get all the proc names that were created or modified by a specific user or from a specific computer in the database.
Thank you
|
|
|
|
|
I would guess you are using Sql Server. AFAIK, there is no way to do so. The sys.procedures table does not show any field that matches what you request.
There is, see post below;
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
modified 15-Sep-15 14:09pm.
|
|
|
|
|
Closest I can think of is to list the SPs by "owner" ...don't think it's what you're after though. E.g.
exec sp_stored_procedures @sp_owner = 'dbo'
Or you could try using a log reader
|
|
|
|
|
SELECT *
FROM sys.fn_dblog(NULL,NULL) ..but only if there's a decent log, and if they did not log in using the sa-account.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
SQL does not! stores information about the person (user) modified any of its object, the only information stored is the last date it modified...
You may look into the trc files of the default trace (you may need to enable it), but it is good for a short time as it being overwritten periodically...
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
Dear all.
Manufacture Model No Length Width height
Manufacture 1 MD-01 200 300 100
Manufacture 1 MD-02 300 400 100
Manufacture 1 MD-03 400 300 150
Manufacture 2 MAD-01 200 450 100
Manufacture 2 MAD-02 250 400 100
Manufacture 3 MDI-01 300 300 100
Manufacture 4 MOv-01 350 300 100
I would like to create 2 combobox -
1) Manufacture 2 ) model No
Manufacture should display for above example Manufacture 1,Manufacture 2,Manufacture 3, Manufacture 4
when particular manufacture selected for example Manufacture 2 -> combox should display only MAD-01 & MAD 02.
Based on both selection text box must me loaded with length, width , height.
i tried sample code & try to assign the combox with database value. I found duplicate list of column. Like for combobox1 accumulate
Manufacture 1
Manufacture 1
Manufacture 1
Manufacture 2
Manufacture 2
Manufacture 3
Manufacture 4
Is there any example program available to do this. How can do this. can some one give example program for this
|
|
|
|
|
Also in the VB-forum; please don't crosspost.
Instead of removing the duplicates, I'd recommend to have a DISTINCT list of the names to load in the combo. Google for "SELECT DISTINCT" to get examples.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
was reading an article about memSQL ... seems to be good and new thing.. but wonder does it make any difference with other documented databases?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
