- MSSQL set null when value is not numeric in select query
SELECT CASE ISNUMERIC(column1) WHEN 1 THEN column1 ELSE NULL END FROM table1
- SQL Server Error Messages – Msg 8152 – String or binary data would be truncated. The statement has been terminated.
This error message is occurred when I tried to insert data to values from another table.It’s due to one column in the table that im inserting data have lesser length than other table column.(varchar(25) – varchar(max)).It couldn’t found previously because second table column I have used max length but when inserting data it filled with strings that contains more than 25 characters.
I found another command to skip that error and continue but its like a try catch statement without catch part(thanks yasindu bro for nice example).
SET ANSI_WARNINGS OFF
Need to use above command before inserting records.but sometimes it’s not working
- Inserting multiple records to a table
This isn’t simple thing but I previously work with MySQL and that is different and we can’t use
INSERT INTO tblName (column1,column2) VALUES ('Value1','value11'), ('Value2','value22'), ('Value3','value33')
INSERT INTO tblName (column1,column2) select ‘Value1′,’Value11’ UNION ALL select ‘Value2′,’Value22’ UNION ALL select ‘Value3′,’Value33’
There are several tables and views containing stored procedures, functions and triggers definitions
Views – SYS.SYSCOMMENTS, SYS.SQL_MODULES
Tables – INFORMATION_SCHEMA.ROUTINES
I recommend following query it shows all SPs and Fns that contains any specific text.If we want to search only procedure then filter in where clause “AND obj.Type=’P’”
SELECT DISTINCT obj.name AS Object_Name,obj.type_desc
FROM sys.sql_modules sm INNER JOIN sys.objects obj ON sm.object_id=obj.object_id
WHERE sm.definition Like ‘%search text%’
If we want to search specific function or objects names we can find dependencies using following system stored procedure
EXEC sp_depends @objname =searchobjectname
There is another way to find specific text using INFORMATION_SCHEMA.ROUTINES. But for me it didn’t give me all the results.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
WHERE ROUTINE_DEFINITION LIKE ‘%search text%’
AND ROUTINE_TYPE=’PROCEDURE’ (for procedures only)
And also SYS.SYSCOMMENTS view can use to same usage but I got duplicates from some results.
SELECT SO.NAME, SC.TEXT
FROM SYS.SYSOBJECTS SO
JOIN SYS.SYSCOMMENTS SC ON SO.ID = SC.ID
WHERE SC.TEXT LIKE ‘%search text%’ –SO.TYPE = ‘P'(for procedures)
Hope this post helps you.Feel free to give your suggestion on this post.Until next post cheers..!!!