|
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
Cleaning Tables | |
What does DBCC CLEANTABLE do? | |
Think you know the answer? Click here, and find out if you are right. |
Yesterday's Question of the Day (by Steve Jones - SSC Editor) |
Stored Procedure Return Values I have this stored procedure code: CREATE PROCEDURE SampleProcedure @EmployeeIDParm INT, @MaxTotal INT OUTPUT AS -- Do a SELECT using the input parameter. SELECT FirstName, LastName, JobTitle FROM HumanResources.vEmployee WHERE EmployeeID = @EmployeeIDParm -- Set a value in the output parameter. SELECT @MaxTotal = MAX(TotalDue) FROM Sales.SalesOrderHeader; GO I call this procedure with this code: DECLARE @return INT , @count INT; EXEC @return = SampleProcedure 12, @count OUTPUT; SELECT @count, @return; What values is in @return Answer: 0 if no error, another value if there is an error. Explanation: If no return value is specified, and the procedure completes, 0 is returned unless you specify a value. If you do not specify a value, then an error value can be returned. Try this: ALTER PROCEDURE SampleProcedure @EmployeeIDParm INT, @MaxTotal INT OUTPUT AS -- Do a SELECT using the input parameter. SELECT FirstName, LastName, JobTitle FROM HumanResources.vEmployee WHERE BusinessEntityID = @EmployeeIDParm -- Set a value in the output parameter. SELECT @MaxTotal = MAX(TotalDue) FROM Sales.SalesOrderHeader; SELECT 1/0 Ref: Stored procedure parameters - https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/parameters?view=sql-server-ver16 |
Database Pros Who Need Your Help |
Here's a few of the new posts today on the forums. To see more, visit the forums. |
SQL Server 2017 - Administration |
Help me understand SSIS Environments - Ok, so I understand what they are, they're collections of parameter assignments that you can tell a package to use upon execution. What I'm trying to understand is why or if I need to use them. I'm migrated a bunch of old SSIS packages using the packages deployment model to some new servers using the […] |
SQL Server 2016 - Administration |
Patch SQL Server Failover Instances separately - Lets say you have a sql failover cluster environment with n nodes (more than 2) and n instances. Instance1 and Instance2 are for developing and testing. Is it possible/ok to patch, for example, Instance1 and Instance2 from SQL Server 2016 SP1 to SQL Server 2016 SP3 and leave the other instances (Instance3, Instance4, ...) on […] |
SQL Server 2016 - Development and T-SQL |
Finding difference in two databases/tables - I have compared two tables with the same data in two different databases(aslo the schema) with SSIS(Comparrison), sql attached. The Schema comparison return an exact match, the table quite a few new updates. The tables are from Production and QA databases. I ran the exact queries on both of these databases, see attached. I am […] |
Development - SQL Server 2014 |
loading data - for suppose, my colleague is loading data in a table at same time i want to read that table without any interrupting his loading? how can i do that? |
SQL Server 2019 - Administration |
how to create index rebuild job on SQL Pas machine - how to create index rebuild job and stats jobs on SQL Pas machine can any one sugegst. |
SQL Server 2019 - Development |
Analyzing execution plan - Hi, can someone help me to understand the following execution plan? I'm not sure why I have at the bottom right Index Seek with cost 99% that returns zero rows? Thanks! |
Substitute 0 and 1 values in 1 column - Hello everyone, Hi have this column named Sex and it only has value 0 and 1. I would like to substitute those values, 1 for female and 0 for male. Could you let me know how to do it in SQL? Thank you all in advance Pedro |
SELECT * FROM ( ... ) X - Hi all! I'm trying to understand how does this work. I've read a thread here about the X thing but it wasn't useful at all. Given this example: DECLARE @PAGE INT=1 WHILE (@PAGE<=5) BEGIN DECLARE @ROWS INT=5 SELECT * FROM ( SELECT ROWNUM = ROW_NUMBER() OVER (ORDER BY CLI_COD), * FROM CLIENTE) X WHERE ROWNUM […] |
Would like to understand a better way of updating data that a cursor if possible - I have a need to update some data and also create a record of that change. From what I know and I don't know a whole hell of a lot, I can do an update with update table set field value where value = value but to make a record of the change I need […] |
SQL Question - Column References - Hi. Looking for someone to help with a query I'm tring to run. I have a table with thousands of rows and need to find all records referenced by one another by a column The table looks like the following: ID Barcode CreateDate OldBarcode 1 101 8/10/2022 NULL 2 102 8/10/2022 NULL 3 103 8/12/2022 […] |
TSQL using Python for market basket analysis ModuleNotFoundError: apyori - Apologies if this is the wrong forum as its really python but need to run from TSQL so have a SQL dataset to pass in, not a CSV file, so am hoping this is a common issue and an easy solution. I have installed Python as a SQL feature, and can do the simple microsoft […] |
How could I have written this script better? - Hi all, I'm looking to sharpen my SQL/Programming skills, below is a sproc I wrote earlier, it's in prod but I can't shake the feeling it's more cobbled together than professional. Looking for any and all critique as to what I could have done better!! TIA ---------------------------------------------------------------------------------------------------------------------------------- --Insert Operator Script (IOS) --The purpose of IOS […] |
Best place to practice - Hi Guys, We are in the process of moving to a new external system and unfortunately I will not have access the SQL server. SQL is only a small part of my role but I really enjoy it. This means I basically wont have opportunity to practice any SQL development. I really don't want to […] |
Reporting Services |
#Error for calculation in a textbox - I am getting the #error when trying to sum a calculated field in ssrs text box. I have seen posts regarding division by zero but this has none of that. The calculated field is appearing correctly in the detail row and I get the error when I try to sum it my equation is =SUM(IIF(Fields!strType.Value="CreditsAndDeposits",Fields!intDT.Value,0)) […] |
Continuous Integration, Deployment, and Delivery |
Database build artefacts: build script, dacpac or .bak file? - Curious to know what everybody's preferred build artefacts are for databases? Say you have your databases represented as scripts in source control, what deployable item is that packaged into? I prefer collating all source-controlled scripts into a single, dependency-ordered, idempotent, build script. Then using that for CI, unit tests. And deploying a specified subset of […] |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |